Skip to content

Index

seamuslee requested to merge seamuslee/pendingmailing:index into master

This indcludes !1 (merged) but also adds in an upgrade step and install and uninstall processes to add an index onto the job log table

We found that when we ran the explain on the query in this extension that checks the job log table that no indexes were being used and for our client it was taking like 9s to process

MariaDB [bcadopt_civicrm]> EXPLAIN SELECT run_time, name, description
    ->           FROM civicrm_job_log
    ->             WHERE job_id = 1 and description like 'Finished execution%'
    ->             AND run_time >= now() - INTERVAL 24 HOUR
    ->           ORDER BY run_time DESC;
+------+-------------+-----------------+------+---------------+------+---------+------+---------+-----------------------------+
| id   | select_type | table           | type | possible_keys | key  | key_len | ref  | rows    | Extra                       |
+------+-------------+-----------------+------+---------------+------+---------+------+---------+-----------------------------+
|    1 | SIMPLE      | civicrm_job_log | ALL  | IN_job_id     | NULL | NULL    | NULL | 3471962 | Using where; Using filesort |
+------+-------------+-----------------+------+---------------+------+---------+------+---------+-----------------------------+
1 row in set (0.002 sec)

Adding in the index contained in this MR changed the explain to be

MariaDB [bcadopt_civicrm]> EXPLAIN SELECT run_time, name, description           FROM civicrm_job_log             WHERE job_id = 1 and description like 'Finished execution%'             AND run_time >= now() - INTERVAL 24 HOUR           O                                                                                RDER BY run_time DESC;
+------+-------------+-----------------+------+-------------------------------------+---------------------------+---------+-------+---------+----------------------------------------------------+
| id   | select_type | table           | type | possible_keys                       | key                       | key_len | ref   | rows    | Extra                                              |
+------+-------------+-----------------+------+-------------------------------------+---------------------------+---------+-------+---------+----------------------------------------------------+
|    1 | SIMPLE      | civicrm_job_log | ref  | IN_job_id,idx_civicrm_job_log_query | idx_civicrm_job_log_query | 5       | const | 1735981 | Using index condition; Using where; Using filesort |
+------+-------------+-----------------+------+-------------------------------------+---------------------------+---------+-------+---------+----------------------------------------------------+
1 row in set (0.002 sec)

and query finished just under 2s

@bgm

Merge request reports