Skip to content
GitLab
Projects Groups Topics Snippets
  • /
  • Help
    • Help
    • Support
    • Community forum
    • Submit feedback
    • Contribute to GitLab
  • Sign in
  • P Pending Mailing
  • Project information
    • Project information
    • Activity
    • Labels
    • Members
  • Repository
    • Repository
    • Files
    • Commits
    • Branches
    • Tags
    • Contributor statistics
    • Graph
    • Compare revisions
  • Issues 0
    • Issues 0
    • List
    • Boards
    • Service Desk
    • Milestones
  • Merge requests 1
    • Merge requests 1
  • CI/CD
    • CI/CD
    • Pipelines
    • Jobs
    • Schedules
  • Deployments
    • Deployments
    • Environments
    • Releases
  • Monitor
    • Monitor
    • Incidents
  • Analytics
    • Analytics
    • Value stream
    • CI/CD
    • Repository
  • Wiki
    • Wiki
  • Snippets
    • Snippets
  • Activity
  • Graph
  • Create a new issue
  • Jobs
  • Commits
  • Issue Boards
Collapse sidebar
  • Extensions
  • Pending Mailing
  • Merge requests
  • !2

Index

  • Review changes

  • Download
  • Email patches
  • Plain diff
Open seamuslee requested to merge seamuslee/pendingmailing:index into master Jan 12, 2023
  • Overview 1
  • Commits 1
  • Pipelines 0
  • Changes 3

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

Assignee
Assign to
Reviewers
Request review from
Time tracking
Source branch: index