CRM-13614 SQL query on dynamic group bracking database

Hi,

We encountered an issue in using dynamic groups.
The built query was killing are mysql database due to huge "left join" usage.
So we made a patch on this file : civicrm/CRM/Contact/BAO/Query.php (function addGroupContactCache)

Below the generated query :

<pre>
INSERT IGNORE INTO civicrm_group_contact_cache (group_id,contact_id) (
SELECT 103 as group_id,
contact_a.id as id
FROM civicrm_contact contact_a
LEFT JOIN civicrm_group_contact `civicrm_group_contact-25,74,97,28,58,33,34,35,39,38,40,41,43,37,44,46,45,47,48,49,50,51,52,53,23,101,102,26` ON contact_a.id = `civicrm_group_contact-25,74,97,28,58,33,34,35,39,38,40,41,43,37,44,46,45,47,48,49,50,51,52,53,23,101,102,26`.contact_id
LEFT JOIN civicrm_group_contact_cache `civicrm_group_contact_cache_33` ON contact_a.id = `civicrm_group_contact_cache_33`.contact_id
LEFT JOIN civicrm_group_contact_cache `civicrm_group_contact_cache_34` ON contact_a.id = `civicrm_group_contact_cache_34`.contact_id
LEFT JOIN civicrm_group_contact_cache `civicrm_group_contact_cache_35` ON contact_a.id = `civicrm_group_contact_cache_35`.contact_id
LEFT JOIN civicrm_group_contact_cache `civicrm_group_contact_cache_37` ON contact_a.id = `civicrm_group_contact_cache_37`.contact_id
LEFT JOIN civicrm_group_contact_cache `civicrm_group_contact_cache_38` ON contact_a.id = `civicrm_group_contact_cache_38`.contact_id
LEFT JOIN civicrm_group_contact_cache `civicrm_group_contact_cache_39` ON contact_a.id = `civicrm_group_contact_cache_39`.contact_id
LEFT JOIN civicrm_group_contact_cache `civicrm_group_contact_cache_40` ON contact_a.id = `civicrm_group_contact_cache_40`.contact_id
LEFT JOIN civicrm_group_contact_cache `civicrm_group_contact_cache_41` ON contact_a.id = `civicrm_group_contact_cache_41`.contact_id
LEFT JOIN civicrm_group_contact_cache `civicrm_group_contact_cache_43` ON contact_a.id = `civicrm_group_contact_cache_43`.contact_id
LEFT JOIN civicrm_group_contact_cache `civicrm_group_contact_cache_44` ON contact_a.id = `civicrm_group_contact_cache_44`.contact_id
LEFT JOIN civicrm_group_contact_cache `civicrm_group_contact_cache_45` ON contact_a.id = `civicrm_group_contact_cache_45`.contact_id
LEFT JOIN civicrm_group_contact_cache `civicrm_group_contact_cache_46` ON contact_a.id = `civicrm_group_contact_cache_46`.contact_id
LEFT JOIN civicrm_group_contact_cache `civicrm_group_contact_cache_47` ON contact_a.id = `civicrm_group_contact_cache_47`.contact_id
LEFT JOIN civicrm_group_contact_cache `civicrm_group_contact_cache_48` ON contact_a.id = `civicrm_group_contact_cache_48`.contact_id
LEFT JOIN civicrm_group_contact_cache `civicrm_group_contact_cache_49` ON contact_a.id = `civicrm_group_contact_cache_49`.contact_id
LEFT JOIN civicrm_group_contact_cache `civicrm_group_contact_cache_50` ON contact_a.id = `civicrm_group_contact_cache_50`.contact_id
LEFT JOIN civicrm_group_contact_cache `civicrm_group_contact_cache_51` ON contact_a.id = `civicrm_group_contact_cache_51`.contact_id
LEFT JOIN civicrm_group_contact_cache `civicrm_group_contact_cache_52` ON contact_a.id = `civicrm_group_contact_cache_52`.contact_id
LEFT JOIN civicrm_group_contact_cache `civicrm_group_contact_cache_53` ON contact_a.id = `civicrm_group_contact_cache_53`.contact_id
WHERE ( ( ( `civicrm_group_contact-25,74,97,28,58,33,34,35,39,38,40,41,43,37,44,46,45,47,48,49,50,51,52,53,23,101,102,26`.group_id IN (
25,74,97,28,58,33,34,35,39,38,40,41,43,37,44,46,45,47,48,49,50,51,52,53,23,101,102,26 )
AND `civicrm_group_contact-25,74,97,28,58,33,34,35,39,38,40,41,43,37,44,46,45,47,48,49,50,51,52,53,23,101,102,26`.status IN ("Added") ) OR ( `civicrm_group_contact_cache_33`.group_id = 33 OR
`civicrm_group_contact_cache_34`.group_id = 34 OR `civicrm_group_contact_cache_35`.group_id = 35 OR `civicrm_group_contact_cache_37`.group_id = 37 OR `civicrm_group_contact_cache_38`.group_id = 38 OR
`civicrm_group_contact_cache_39`.group_id = 39 OR `civicrm_group_contact_cache_40`.group_id = 40 OR `civicrm_group_contact_cache_41`.group_id = 41 OR `civicrm_group_contact_cache_43`.group_id = 43 OR
`civicrm_group_contact_cache_44`.group_id = 44 OR `civicrm_group_contact_cache_45`.group_id = 45 OR `civicrm_group_contact_cache_46`.group_id = 46 OR `civicrm_group_contact_cache_47`.group_id = 47 OR
`civicrm_group_contact_cache_48`.group_id = 48 OR `civicrm_group_contact_cache_49`.group_id = 49 OR `civicrm_group_contact_cache_50`.group_id = 50 OR `civicrm_group_contact_cache_51`.group_id = 51 OR
`civicrm_group_contact_cache_52`.group_id = 52 OR `civicrm_group_contact_cache_53`.group_id = 53 ) ) ) AND (contact_a.is_deleted = 0) AND contact_a.id NOT IN (
SELECT contact_id FROM civicrm_group_contact
WHERE civicrm_group_contact.status = 'Removed'
AND civicrm_group_contact.group_id = 103 ) )

</pre>

As you can see, there is an overhead use of "LEFT JOIN".
Our patch limits this use to only one "LEFT JOIN" and use "IN (....)" instead of "OR" which is the same thing but much pretty to read.