Body: <p>While investigating a CiviCRM performance issue, I came across this query:</p>
  <pre><code>SELECT *
  FROM `civicrm`.`log_civicrm_activity_contact`
  WHERE log_conn_id = ?
         AND id = ?
  ORDER BY  log_date DESC limit 1
  </code></pre>
  <p>The query seems to be definied here:</p>
  <p><a href="https://github.com/civicrm/civicrm-core/blob/03e4bc803dd8978fef913b980538af61ace0c0fd/CRM/Logging/Differ.php#L172" rel="nofollow noreferrer">https://github.com/civicrm/civicrm-core/blob/03e4bc803dd8978fef913b980538af61ace0c0fd/CRM/Logging/Differ.php#L172</a></p>
  <p>It was performing very slowly, since there appear to be no indexes defined on the <code>log_civicrm_activity_contact</code> table:</p>
  <pre><code>CREATE TABLE `log_civicrm_activity_contact` (
    `id` int(10) unsigned DEFAULT NULL COMMENT 'Activity contact id',
    `activity_id` int(10) unsigned DEFAULT NULL COMMENT 'Foreign key to the activity for this record.',
    `contact_id` int(10) unsigned DEFAULT NULL COMMENT 'Foreign key to the contact for this record.',
    `record_type_id` int(10) unsigned DEFAULT NULL COMMENT 'Nature of this contact''s role in the activity: 1 assignee, 2 creator, 3 focus or target.',
    `log_date` timestamp NOT NULL DEFAULT current_timestamp(),
    `log_conn_id` varchar(17) COLLATE utf8_unicode_ci DEFAULT NULL,
    `log_user_id` int(11) DEFAULT NULL,
    `log_action` enum('Initialization','Insert','Update','Delete') COLLATE utf8_unicode_ci DEFAULT NULL
  );
  </code></pre>
  <p>The following index fixes the problem:</p>
  <pre><code>CREATE INDEX idx1 ON log_civicrm_activity_contact (log_conn_id, id, log_date);
  </code></pre>
  <p>On the data set I am working with, without the index, the query takes about 4 seconds.
  With the above index added, it takes under 1ms.</p>
  <p>Does adding this index upstream seem reasonable?</p>
  <p>Since the code in that file looks to be pretty generic across multiple similar log tables (the table name is dynamically generated), it may be useful to add it across all of them. The specific case I have been looking at only heavily exercises this particular table, but I don't know whether similar usage pattern is typical across all similar tables.</p>
  
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2021-04-29T13:16:06.147Z
Comments.Text: I believe there are no indexes on the log tables on purpose because it would increase write time during normal usage.
Comments.UserDisplayName: Demerit
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2021-04-29T13:23:23.747Z
Comments.Text: @Demerit it seems like a poor tradeoff. The server I'm troubleshooting here took hours to complete what should have taken seconds. The queries are part of standard civi so IMO indexes to support those queries should also be part of the standard deployment.
Comments.UserDisplayName: Gordan Bobić
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2021-04-29T13:26:47.457Z
Comments.Text: What action was it / what page was this on?
Comments.UserDisplayName: Demerit
Tags: <database><mysql><issues><performance><mariadb>
Title: Poorly performing unindexed query on log tables
ThreadText: # Poorly performing unindexed query on log tables
  
  (Asked by: username_0 on 2021-04-29)
  
  While investigating a CiviCRM performance issue, I came across this query:
  SELECT *
  FROM `civicrm`.`log_civicrm_activity_contact`
  WHERE log_conn_id = ?
         AND id = ?
  ORDER BY  log_date DESC limit 1
  
  The query seems to be definied here:
  https://github.com/civicrm/civicrm-core/blob/03e4bc803dd8978fef913b980538af61ace0c0fd/CRM/Logging/Differ.php#L172
  It was performing very slowly, since there appear to be no indexes defined on the log_civicrm_activity_contact table:
  CREATE TABLE `log_civicrm_activity_contact` (
    `id` int(10) unsigned DEFAULT NULL COMMENT 'Activity contact id',
    `activity_id` int(10) unsigned DEFAULT NULL COMMENT 'Foreign key to the activity for this record.',
    `contact_id` int(10) unsigned DEFAULT NULL COMMENT 'Foreign key to the contact for this record.',
    `record_type_id` int(10) unsigned DEFAULT NULL COMMENT 'Nature of this contact''s role in the activity: 1 assignee, 2 creator, 3 focus or target.',
    `log_date` timestamp NOT NULL DEFAULT current_timestamp(),
    `log_conn_id` varchar(17) COLLATE utf8_unicode_ci DEFAULT NULL,
    `log_user_id` int(11) DEFAULT NULL,
    `log_action` enum('Initialization','Insert','Update','Delete') COLLATE utf8_unicode_ci DEFAULT NULL
  );
  
  The following index fixes the problem:
  CREATE INDEX idx1 ON log_civicrm_activity_contact (log_conn_id, id, log_date);
  
  On the data set I am working with, without the index, the query takes about 4 seconds.
  With the above index added, it takes under 1ms.
  Does adding this index upstream seem reasonable?
  Since the code in that file looks to be pretty generic across multiple similar log tables (the table name is dynamically generated), it may be useful to add it across all of them. The specific case I have been looking at only heavily exercises this particular table, but I don't know whether similar usage pattern is typical across all similar tables.
  
  **Tags:** database, mysql, issues, performance, mariadb
  
  > **Comments:**
  > **username_1**: I believe there are no indexes on the log tables on purpose because it would increase write time during normal usage.  
  > **username_0**: @username_1 it seems like a poor tradeoff. The server I'm troubleshooting here took hours to complete what should have taken seconds. The queries are part of standard civi so IMO indexes to support those queries should also be part of the standard deployment.  
  > **username_1**: What action was it / what page was this on?  
  
  ---
  
  ## 0 answers
  
  