Details
-
Type: Bug
-
Status: Done/Fixed
-
Priority: Major
-
Resolution: Fixed/Completed
-
Affects Version/s: 3.0.2
-
Fix Version/s: 3.0.1
-
Component/s: Core CiviCRM
-
Labels:None
Description
We only have about 4000 activities, but the system just dies with the additions made to full-text search. Looking at these queries shows they are written "old style" with comma inner joins which can be really inefficient.
For example this one:
$contactSQL[] = "
SELECT ca.id
FROM civicrm_activity ca, civicrm_activity_target cat, civicrm_contact c, civicrm_email e
LEFT JOIN civicrm_option_group og ON og.name = 'activity_type'
LEFT JOIN civicrm_option_value ov ON ( ov.option_group_id = og.id )
WHERE cat.activity_id = ca.id
AND cat.target_contact_id = c.id
AND ( c.display_name LIKE {$this->_text} OR
( e.email LIKE {$this->_text} AND cat.target_contact_id = e.contact_id AND
ca.activity_type_id = ov.value AND ov.name IN ('Inbound Email', 'Email') ) )
";
should be rewritten more like:
SELECT ca.id
FROM civicrm_activity ca INNER JOIN civicrm_activity_target cat ON cat.activity_id = ca.id
INNER JOIN civicrm_contact c ON cat.target_contact_id = c.id
INNER JOIN civicrm_email e ON cat.target_contact_id = e.contact_id
LEFT JOIN civicrm_option_group og ON og.name = 'activity_type'
LEFT JOIN civicrm_option_value ov ON ( ov.option_group_id = og.id AND ca.activity_type_id = ov.value )
WHERE ( c.display_name LIKE {$this->_text} OR
( e.email LIKE {$this->_text} AND ov.name IN ('Inbound Email', 'Email') ) )