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:
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.
