Details
-
Type: Improvement
-
Status: Open
-
Priority: Major
-
Resolution: Unresolved
-
Affects Version/s: 4.7
-
Fix Version/s: None
-
Component/s: Core CiviCRM
-
Labels:
-
Versioning Impact:Patch (backwards-compatible bug fixes)
-
Documentation Required?:User and Admin Doc
-
Funding Source:Needs Funding
-
Verified?:No
Description
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.