What can be done to improve loading of Activity searches and tabs on databases with gazillion civicrm_activity_contact rows

Question

Digging in to why one of our larger clients is suffering more from slowness of Activity searches or loading of Activity tab for contacts.

Comparing it to other dbs what stands out for this database is the sheer number of rows in civicrm_activity_contact - and while they also have a lot of Activities, they have no where near as many as other databases. A sample of data below where A is the db that is grinding slowly

| db | Activities | Activity Contacts | average contacts per activity |

| A | 626,145 | 22,270,065 | 36 |

| B | 180,000 | 5,680,000 | 32 |

| C | 3,718,634 | 7,722,346 | 2 |

And it is DB 'A' that is seriously slow running Activity related queries.

Most of the Activity Contacts are on 'bulk mail' Activities

The activity search makes use of multiple joins of civicrm_activity, civicrm_activity_contact and civicrm_contact table. It leads to millions and millions of rows again and again which causes an indefinite loop of the query which never finishes or takes much longer time to complete. A sample query which is executed by hitting the search button on Find Activity form.

SELECT count( DISTINCT ( civicrm_activity.id ) ) as rowCount  

FROM civicrm_contact contact_a
  LEFT JOIN civicrm_activity_contact ON ( civicrm_activity_contact.contact_id = contact_a.id )  

  LEFT JOIN civicrm_activity ON ( civicrm_activity.id = civicrm_activity_contact.activity_id
     AND civicrm_activity.is_deleted = 0 AND civicrm_activity.is_current_revision = 1 ) 

  INNER JOIN civicrm_contact ON ( civicrm_activity_contact.contact_id = civicrm_contact.id and civicrm_contact.is_deleted != 1 ) 

  LEFT JOIN civicrm_option_group option_group_activity_type ON (option_group_activity_type.name = 'activity_type') 

  LEFT JOIN civicrm_option_value activity_type ON (civicrm_activity.activity_type_id = activity_type.value 
      AND option_group_activity_type.id = activity_type.option_group_id ) 

  LEFT JOIN civicrm_activity_contact ac ON ( ac.activity_id = civicrm_activity_contact.activity_id AND ac.record_type_id = 2)

  INNER JOIN civicrm_contact source_contact ON (ac.contact_id = source_contact.id) 

WHERE  (  civicrm_activity_contact.record_type_id = 3 
    AND civicrm_activity.is_test = 0 
    AND civicrm_activity.status_id IN ("1", "2") )  
    AND  ( 1 )  
    AND  (activity_type.component_id IS NULL OR activity_type.component_id <> 5)  
    AND  (activity_type.component_id IS NULL OR activity_type.component_id <> 7)

Answer 1

I do not know why this is so slow but we developed an extension together with Matthew Wire that improves some of those problems. Information can be found here.