1

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.

3
  • I believe there are no indexes on the log tables on purpose because it would increase write time during normal usage. – Demerit Apr 29 at 13:16
  • @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. – Gordan Bobic Apr 29 at 13:23
  • What action was it / what page was this on? – Demerit Apr 29 at 13:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.