Performance improvement on civicrm_acl_contact_cache

At the socialist party local chapter members could login and see their local members. This done by using the hook hook_civicrm_aclWhereClause in this hook we lookup the chapter of the local user and then we add a part to the query to lookup people living in the same area and who have an active membership.

The query resulting is a query with a lot of subqueries which makes it a bad performing query. For example when a user does a search the search query self is altered with the hook_civicrm_aclWhereClauses from the hook. During a search at least three queries are altered by this hook: the total amount of records, the paging per first letter of the name, and the actual contacts in the search. So when a user does a search the bad performing query runs three times.
The bad performance is caused by the fact that a search query has to go through all contact records in the database (which are at the socialist party around 360.000)

Proposed solution

• Make a table which stores the user ID and then the contact id a user is allowed to see/edit.
• When a search/report etc is done only use an inner join on this table (inner join perform faster because MySQL will only check the records in the subset)
• Update this table once in a while (for example after 24 hours); This update is done by deleting all records for that user and insert the records based on the query generated by hook_civicrm_aclWhereClause. This is possibly done when a user logs in.
• A user interface to change the validity period (default 24 hours)
• Also refactor/remove the current civicrm_acl_contact_cache as that one is probably obsolete with the above code.