CiviCRM Community Forums (archive)

Hello there, I've run across a very slow (caused mysql to freeze our server) query in CRM/Activity/BAO/Activity.php and I have a tentative fix for it. The diff is below. Things seem to work okay on our installation with the diff applied.

Let me know if anyone sees any flaws that need fixing. Basically I removed what I thought to be superfluous left joins and the all the aliases (so I could figure out what was going on).   

--- Desktop/civicrm/CRM/Activity/BAO/Activity.php       2009-09-11 05:56:50.000000000 -0700
+++ NetBeansProjects/GW/sites/all/modules/civicrm/CRM/Activity/BAO/Activity.php 2009-10-03 10:33:51.000000000 -0700
@@ -559,8 +559,8 @@ class CRM_Activity_BAO_Activity extends
                                                               'name' );
         if ( !$admin ) {
             $clause = " ( source_contact_id = %1 or
-                          at.target_contact_id = %1 or
-                          aa.assignee_contact_id = %1 or
+                          civicrm_activity_target.target_contact_id = %1 or
+                          civicrm_activity_assignment.assignee_contact_id = %1 or
                           civicrm_case_contact.contact_id = %1 ) ";
             
             $params = array( 1 => array( $data['contact_id'], 'Integer' ) );
@@ -600,37 +600,29 @@ class CRM_Activity_BAO_Activity extends
         }
         
         if ( $onlyCount ) {
-            $select = "select COUNT(DISTINCT(civicrm_activity.id)) as count";
+            $select = "select COUNT(DISTINCT(civicrm_activity.id)) as count ";
         } else {
             $select ="select DISTINCT(civicrm_activity.id),
                          civicrm_activity.activity_date_time,
                          civicrm_activity.status_id, civicrm_activity.subject,
                          civicrm_activity.source_contact_id,civicrm_activity.source_record_id,
-                         sourceContact.sort_name as source_contact_name,
-                         GROUP_CONCAT( DISTINCT(activity_target.target_contact_id ) SEPARATOR '::') as target_contact_id,
-                         GROUP_CONCAT( DISTINCT(targetContact.sort_name ) SEPARATOR '::') as target_contact_name,
-                         GROUP_CONCAT( DISTINCT(activity_assignment.assignee_contact_id  ) SEPARATOR '::' ) as assignee_contact_id,
-                         GROUP_CONCAT( DISTINCT(assigneeContact.sort_name ) SEPARATOR '::' ) as assignee_contact_name,
+                         civicrm_contact.sort_name as source_contact_name,
+                         GROUP_CONCAT( DISTINCT(civicrm_activity_target.target_contact_id ) SEPARATOR '::') as target_contact_id,
+                         GROUP_CONCAT( DISTINCT(civicrm_contact.sort_name ) SEPARATOR '::') as target_contact_name,
+                         GROUP_CONCAT( DISTINCT(civicrm_activity_assignment.assignee_contact_id  ) SEPARATOR '::' ) as assignee_contact_id,
+                         GROUP_CONCAT( DISTINCT(civicrm_contact.sort_name ) SEPARATOR '::' ) as assignee_contact_name,
                          civicrm_option_value.value as activity_type_id,
                          civicrm_option_value.label as activity_type,
                          civicrm_case_activity.case_id as case_id,
                          civicrm_case.subject as case_subject ";
         }
-        $join   = "\n
-                  left join civicrm_activity_target at on
-                            civicrm_activity.id = at.activity_id
-                  left join civicrm_activity_assignment aa on
-                            civicrm_activity.id = aa.activity_id
-                  left join civicrm_activity_target as activity_target on
-                            civicrm_activity.id = activity_target.activity_id
-                  left join civicrm_activity_assignment as activity_assignment on
-                            civicrm_activity.id = activity_assignment.activity_id                               
-                  left join civicrm_contact sourceContact on
-                            source_contact_id = sourceContact.id
-                         left join civicrm_contact targetContact on
-                            activity_target.target_contact_id = targetContact.id
-                  left join civicrm_contact assigneeContact on
-                            activity_assignment.assignee_contact_id = assigneeContact.id
+        $join   = "
+                  left join civicrm_activity_target on
+                            civicrm_activity.id = civicrm_activity_target.activity_id
+                  left join civicrm_activity_assignment on
+                            civicrm_activity.id = civicrm_activity_assignment.activity_id
+                  left join civicrm_contact on
+                            source_contact_id = civicrm_contact.id
                   left join civicrm_option_value on
                             ( civicrm_activity.activity_type_id = civicrm_option_value.value )
                   left join civicrm_option_group on 
@@ -640,8 +632,7 @@ class CRM_Activity_BAO_Activity extends
                   left join civicrm_case on
                             civicrm_case_activity.case_id = civicrm_case.id
                   left join civicrm_case_contact on
-                            civicrm_case_contact.case_id = civicrm_case.id ";
-       
+                          civicrm_case_contact.case_id = civicrm_case.id ";     
         $from  = " from civicrm_activity ";
         $where = " where {$clause}
                    and civicrm_option_group.name = 'activity_type'