Answers.Body: <p>Try this:</p>
  <pre><code>SELECT 
      civicrm_activity.id,
      contact_a.id AS contact_id,
      contact_a.contact_type AS contact_type,
      contact_a.contact_sub_type AS contact_sub_type,
      contact_a.sort_name AS sort_name,
      contact_a.display_name AS display_name,
      civicrm_activity.activity_type_id,
      civicrm_activity.subject AS activity_subject,
      civicrm_activity.activity_date_time AS activity_date_time,
      civicrm_activity.status_id AS activity_status_id,
      civicrm_activity.source_record_id AS source_record_id,
      civicrm_activity.is_test AS activity_is_test,
      civicrm_activity.campaign_id AS activity_campaign_id,
      civicrm_activity.engagement_level AS activity_engagement_level,
      source_contact.sort_name AS source_contact
  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
  LEFT JOIN 
      civicrm_activity_contact source_activity ON source_activity.activity_id = civicrm_activity_contact.activity_id
      AND source_activity.record_type_id = 2
  LEFT JOIN 
      civicrm_contact source_contact ON source_activity.contact_id = source_contact.id
  WHERE  
      civicrm_activity.activity_type_id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 33, 34, 35, 36, 37, 38, 39, 40, 41, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 57, 59, 61, 63, 65, 66, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 79, 80, 81, 82, 83, 84, 85, 86, 87, 89, 90, 91, 92, 93, 94, 95, 96, 97, 98, 99, 100, 101, 102, 103, 104)
      AND civicrm_activity.is_test = 0 
      AND contact_a.is_deleted = 0 
  GROUP BY 
      civicrm_activity.id
  ORDER BY 
      activity_date_time DESC, 
      contact_a.id
  LIMIT 50;
  </code></pre>
  <ol>
  <li><strong>Removed Redundant Fields:</strong> <code>activity_type_id</code>, <code>status_id</code>, and <code>engagement_level</code> are selected twice in the query, so we can eliminate the duplicates.</li>
  <li><strong>Optimized Filters in <code>WHERE</code> Clause:</strong> In <code>WHERE</code>, <code>activity_type_id IN (95)</code> and the long <code>activity_type_id</code> list can be simplified to remove <code>95</code> if already covered by the larger list.</li>
  <li><strong>Removed Redundant <code>INNER JOIN</code> Condition:</strong> The line <code>INNER JOIN civicrm_contact ON civicrm_activity_contact.contact_id = civicrm_contact.id</code> appears redundant if <code>contact_a</code> already represents this data.</li>
  <li><strong>Duplicate Data:</strong> Since <code>GROUP BY</code> is used on <code>civicrm_activity.id</code>, it may be worth only including fields from <code>civicrm_activity</code> that are truly needed.</li>
  </ol>
  <p>Finally, indexing the columns used in the <code>WHERE</code>, <code>JOIN</code> and <code>ORDER BY</code> clauses should speed things up as well.</p>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2024-11-09T21:17:59.510Z
Answers.Text: These are useful comments but the query is likely coming from civi as the result of some search function and can't be changed, so I think the poster is asking for what kinds of things they can do either in the UI or at the hosting level. Note also the index issue is a known problem because actually adding indexes on e.g. is_deleted/is_current_revision is somewhat useless, because the majority of records have is_deleted=0 and is_current_revision=1, so it can't use the index and does a full table scan. If can find in the code where to implement your suggestions that would of course be good too.
Answers.UserDisplayName: Demerit
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2024-11-09T00:17:40.010Z
Answers.LastActivityDate: 2024-11-09T01:08:10.993Z
Answers.LastEditDate: 2024-11-09T01:08:10.993Z
Answers.UserDisplayName: Bloodstripe
Body: <p>A search for activities of a certain type (ID 95) on a large site takes a long time. The <code>civicrm_activity</code> table has 3,836,452 rows. The machine is a VPN with 6 CPU cores and 8GB memory.</p>
  <p>It appears the bottleneck is this query:</p>
  <pre><code>SELECT 
      civicrm_activity.id,
      contact_a.id AS contact_id,
      contact_a.contact_type AS contact_type,
      contact_a.contact_sub_type AS contact_sub_type,
      contact_a.sort_name AS sort_name,
      contact_a.display_name AS display_name,
      civicrm_activity.id AS activity_id,
      civicrm_activity.activity_type_id,
      civicrm_activity.subject AS activity_subject,
      civicrm_activity.activity_date_time AS activity_date_time,
      civicrm_activity.status_id AS activity_status_id,
      civicrm_activity.source_record_id AS source_record_id,
      civicrm_activity.is_test AS activity_is_test,
      civicrm_activity.campaign_id AS activity_campaign_id,
      civicrm_activity.engagement_level AS activity_engagement_level,
      source_contact.sort_name AS source_contact,
      civicrm_activity.status_id AS status_id,
      civicrm_activity.engagement_level AS engagement_level
  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_activity_contact source_activity ON source_activity.activity_id = civicrm_activity_contact.activity_id
      AND source_activity.record_type_id = 2
  LEFT JOIN 
      civicrm_contact source_contact ON source_activity.contact_id = source_contact.id
  WHERE  
      civicrm_activity.activity_type_id IN (95) 
      AND civicrm_activity.is_test = 0 
      AND contact_a.is_deleted = 0 
      AND civicrm_activity.activity_type_id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 33, 34, 35, 36, 37, 38, 39, 40, 41, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 57, 59, 61, 63, 65, 66, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 79, 80, 81, 82, 83, 84, 85, 86, 87, 89, 90, 91, 92, 93, 94, 95, 96, 97, 98, 99, 100, 101, 102, 103, 104)
  GROUP BY 
      civicrm_activity.id
  ORDER BY 
      activity_date_time DESC, 
      contact_a.id
  LIMIT 0, 50;
  </code></pre>
  <p>which takes 65 seconds.</p>
  <p>The last AND clause of the WHERE clause is superfluous, but anyhow, we will try to add more memory and see if increasing Innodb_buffer_pool_size will help.</p>
  <p>Beyond that, does anyone have any suggestions how to improve the speed here?</p>
  
Tags: <mysql>
Title: How to Optimize MySQL for our Site
ThreadText: # How to Optimize MySQL for our Site
  
  (Asked by: username_0 on 2024-11-06)
  
  A search for activities of a certain type (ID 95) on a large site takes a long time. The `civicrm_activity` table has 3,836,452 rows. The machine is a VPN with 6 CPU cores and 8GB memory.
  
  It appears the bottleneck is this query:
  
  ```
  SELECT 
      civicrm_activity.id,
      contact_a.id AS contact_id,
      contact_a.contact_type AS contact_type,
      contact_a.contact_sub_type AS contact_sub_type,
      contact_a.sort_name AS sort_name,
      contact_a.display_name AS display_name,
      civicrm_activity.id AS activity_id,
      civicrm_activity.activity_type_id,
      civicrm_activity.subject AS activity_subject,
      civicrm_activity.activity_date_time AS activity_date_time,
      civicrm_activity.status_id AS activity_status_id,
      civicrm_activity.source_record_id AS source_record_id,
      civicrm_activity.is_test AS activity_is_test,
      civicrm_activity.campaign_id AS activity_campaign_id,
      civicrm_activity.engagement_level AS activity_engagement_level,
      source_contact.sort_name AS source_contact,
      civicrm_activity.status_id AS status_id,
      civicrm_activity.engagement_level AS engagement_level
  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_activity_contact source_activity ON source_activity.activity_id = civicrm_activity_contact.activity_id
      AND source_activity.record_type_id = 2
  LEFT JOIN 
      civicrm_contact source_contact ON source_activity.contact_id = source_contact.id
  WHERE  
      civicrm_activity.activity_type_id IN (95) 
      AND civicrm_activity.is_test = 0 
      AND contact_a.is_deleted = 0 
      AND civicrm_activity.activity_type_id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 33, 34, 35, 36, 37, 38, 39, 40, 41, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 57, 59, 61, 63, 65, 66, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 79, 80, 81, 82, 83, 84, 85, 86, 87, 89, 90, 91, 92, 93, 94, 95, 96, 97, 98, 99, 100, 101, 102, 103, 104)
  GROUP BY 
      civicrm_activity.id
  ORDER BY 
      activity_date_time DESC, 
      contact_a.id
  LIMIT 0, 50;
  
  ```
  
  which takes 65 seconds.
  
  The last AND clause of the WHERE clause is superfluous, but anyhow, we will try to add more memory and see if increasing Innodb\_buffer\_pool\_size will help.
  
  Beyond that, does anyone have any suggestions how to improve the speed here?
  
  **Tags:** mysql
  
  
  ---
  
  ## 1 answers
  
  ### Answer by username_1 on 2024-11-09. Score: 0
  
  Try this:
  
  ```
  SELECT 
      civicrm_activity.id,
      contact_a.id AS contact_id,
      contact_a.contact_type AS contact_type,
      contact_a.contact_sub_type AS contact_sub_type,
      contact_a.sort_name AS sort_name,
      contact_a.display_name AS display_name,
      civicrm_activity.activity_type_id,
      civicrm_activity.subject AS activity_subject,
      civicrm_activity.activity_date_time AS activity_date_time,
      civicrm_activity.status_id AS activity_status_id,
      civicrm_activity.source_record_id AS source_record_id,
      civicrm_activity.is_test AS activity_is_test,
      civicrm_activity.campaign_id AS activity_campaign_id,
      civicrm_activity.engagement_level AS activity_engagement_level,
      source_contact.sort_name AS source_contact
  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
  LEFT JOIN 
      civicrm_activity_contact source_activity ON source_activity.activity_id = civicrm_activity_contact.activity_id
      AND source_activity.record_type_id = 2
  LEFT JOIN 
      civicrm_contact source_contact ON source_activity.contact_id = source_contact.id
  WHERE  
      civicrm_activity.activity_type_id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 33, 34, 35, 36, 37, 38, 39, 40, 41, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 57, 59, 61, 63, 65, 66, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 79, 80, 81, 82, 83, 84, 85, 86, 87, 89, 90, 91, 92, 93, 94, 95, 96, 97, 98, 99, 100, 101, 102, 103, 104)
      AND civicrm_activity.is_test = 0 
      AND contact_a.is_deleted = 0 
  GROUP BY 
      civicrm_activity.id
  ORDER BY 
      activity_date_time DESC, 
      contact_a.id
  LIMIT 50;
  
  ```
  
  1. **Removed Redundant Fields:** `activity_type_id`, `status_id`, and `engagement_level` are selected twice in the query, so we can eliminate the duplicates.
  2. **Optimized Filters in `WHERE` Clause:** In `WHERE`, `activity_type_id IN (95)` and the long `activity_type_id` list can be simplified to remove `95` if already covered by the larger list.
  3. **Removed Redundant `INNER JOIN` Condition:** The line `INNER JOIN civicrm_contact ON civicrm_activity_contact.contact_id = civicrm_contact.id` appears redundant if `contact_a` already represents this data.
  4. **Duplicate Data:** Since `GROUP BY` is used on `civicrm_activity.id`, it may be worth only including fields from `civicrm_activity` that are truly needed.
  
  Finally, indexing the columns used in the `WHERE`, `JOIN` and `ORDER BY` clauses should speed things up as well.
  
  > **Comments:**
  > **username_2**: These are useful comments but the query is likely coming from civi as the result of some search function and can't be changed, so I think the poster is asking for what kinds of things they can do either in the UI or at the hosting level. Note also the index issue is a known problem because actually adding indexes on e.g. is_deleted/is_current_revision is somewhat useless, because the majority of records have is_deleted=0 and is_current_revision=1, so it can't use the index and does a full table scan. If can find in the code where to implement your suggestions that would of course be good too.  
  
  ---
  
  