The last two days we spent another sprint at socialist party to improve performance. And again we used a guy with database knowledge to analyze our queries. We have optimized a few things so far one of the major areas to improve next was the ACL part of queries. That is the part of the query to decide which contacts you are allowed to see. So at the socialist party they have local chapter administrator who are only allowed to see the active members in their local area.
To decide which contacts you are allowed to see a search query is rebuild to include this ACL part. So there is a join on the membership table and a join on the chapter-structure table and then a where expression is to filter on the chapter and to filter on active memberships. Meaning that the MySQL will scan all records in the table civicrm_contact which at the socialist party is around 350.000.
We have worked on a proof-of-concept solution so far which is to create a table with the user_id and which contact_ids the user is allowed to see. We then do a join on this table resulting that only that subset of contacts is scanned by mysql. Which in fact improved the search query from running a few seconds to just a few milliseconds.
When a user logs in we check how long ago it was when we last updated this table and if it is longer than 24 hours we will delete the entries for that user and re-insert the entries.
We have also reported this at the CiviCRM issue Queue see: https://issues.civicrm.org/jira/browse/CRM-19934
The next thing we are going to work on is to make the proof-of-concept stable and do some refactoring of the core civicrm_acl_contact_cache table. As this proof-of-concept looks like a bit what civicrm_acl_contact_cache should do but does not do. Also we want to add a setting in the user interface where site-administrators could set the value for the validity time.
We are also wondering what your thoughts are about our performance quest so far and about our proposed solution.
