Details
-
Type: Improvement
-
Status: Done/Fixed
-
Priority: Minor
-
Resolution: Fixed/Completed
-
Affects Version/s: 4.3.3
-
Fix Version/s: 4.4.4
-
Component/s: None
-
Labels:None
Description
I noticed that there in 4.3.3 there is a created_date column in the contact table, but that the advanced search does not use it. Instead, if I filter contacts by added and "previous two months", the server starts crunching away on a very demanding query that uses the activity log tables and can take minutes to complete:
SELECT DISTINCT UPPER(LEFT(contact_a.sort_name, 1)) as sort_name FROM civicrm_contact contact_a LEFT JOIN civicrm_log ON (civicrm_log.entity_id = contact_a.id AND civicrm_log.entity_table = 'civicrm_contact') LEFT JOIN civicrm_contact contact_b_log ON (civicrm_log.modified_id = contact_b_log.id) WHERE (
( civicrm_log.modified_date >= '20130401000000' ) AND
( civicrm_log.modified_date <= '20130531235959' )
AND civicrm_log.id IN ( select id from civicrm_log group by entity_id order by id ) ) AND (contact_a.is_deleted = 0) ORDER BY UPPER(LEFT(contact_a.sort_name, 1)) asc
One obvious issue here is the nested query, which MySQL dislikes, but also, it should have just used created_date in the contacts table. Same for modified date.
Also, note that when toggling between "Added" and "Modified", the caption is still "Modified by" and "Modified between". I would suggest adding controls for Created Date, Last Modified Date inside the "Basic Criteria" Section of the form.