Monday, December 19, 2016 - 09:14
Written by

Last week we had our third sprint at the socialist party to improve the performance.  In the previous blogs I have explained what we have done so far. You can read them here and here.

Since the previous sprint a guy with a lot of database knowledge has done some analysis of the queries and he came up with the following observations. Most queries are build in CRM_Contact_BAO_Query class and that class adds a join on the tables civicrm_group_contact and civicrm_group_contact_cache and a where clause with an or on both tables.  See example query below.

SELECT AS contact_id  FROM civicrm_contact contact_a  LEFT JOIN civicrm_group_contact `civicrm_group_contact-2304` ON ( = `civicrm_group_contact-2304`.contact_id AND `civicrm_group_contact-2304`.status IN ("Added"))   LEFT JOIN civicrm_group_contact_cache `civicrm_group_contact_cache_2304` ON = `civicrm_group_contact_cache_2304`.contact_id   WHERE  ( ( ( `civicrm_group_contact-2304`.group_id IN ( 2304 ) ) OR ( `civicrm_group_contact_cache_2304`.group_id IN (2304) ) ) )   GROUP BY;

Such a query is performing slow because it is looking up all records in civicrm_contact, all records in civicrm_group_contact_cache and all records civicrm_group_contact. Rebuilding the query to use an IN statement will increase the performance. See the query below:

SELECT AS contact_id  FROM civicrm_contact contact_a 
WHERE IN (SELECT contact_id FROM civicrm_group_contact WHERE group_id IN (2304) AND status IN ("Added"))
OR IN (SELECT contact_id FROM civicrm_group_contact_cache ON groupId IN (2304))

Luckily we could do this in an extension because CRM_Contact_BAO_Query provides functionality to change and tweak the query.  The extension we have developed could be found on github (

In our test environment the extension has a visible effect on the performance. Searching for contact went down from 15 seconds to 3 seconds. We have also installed the extension in the production environment and we are waiting on feedback from end users whether they experience a noticble increase in performance.

Read more


Filed under


Are you using 4.6? I removed the hurtful OR clause in that query in 4.7

I can't find the actual ticket it was changed on but you can see recent discussion on the changes made here


I think I fixed it about 6 months ago?

Yes we are using 4.6.

Did the "a guy with a lot of database knowledge" not want that his name was mentioned to get credit for his work?