CiviCRM Community Forums (archive)

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 :


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 ) )



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.

Should it be possible to contribute by providing the patch for both 4.2 and 4.3 releases ?
If so, what is the procedure ?

Thanks