Make log deletion more efficient on the DB

According to New Relic, the regular cron job that checks if logs should be cleaned up from the database is really inefficient. We can see that here: MySQL [bitnami_wordpress]> EXPLAIN ANALYZE -> SELECT -> * -> FROM -> wp_cron_logs -> WHERE -> parent_id IN ( -> SELECT -> id -> FROM -> ( -> SELECT -> id -> FROM -> wp_cron_logs -> WHERE -> parent_id IS NULL -> AND executed < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 30 day)) -> ) as parent_id -> ); +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | -> Nested loop inner join (cost=87254.10 rows=9616) (actual time=0.046..686.448 rows=5723 loops=1) -> Filter: (wp_cron_logs.parent_id is not null) (cost=19944.55 rows=192313) (actual time=0.027..245.707 rows=169347 loops=1) -> Table scan on wp_cron_logs (cost=19944.55 rows=192313) (actual time=0.021..169.521 rows=191038 loops=1) -> Filter: ((wp_cron_logs.parent_id is null) and (wp_cron_logs.executed < <cache>(unix_timestamp((now() - interval 30 day))))) (cost=0.25 rows=0) (actual time=0.002..0.002 rows=0 loops=169347) -> Single-row index lookup on wp_cron_logs using PRIMARY (id=wp_cron_logs.parent_id) (cost=0.25 rows=1) (actual time=0.001..0.001 rows=1 loops=169347) | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.690 sec) MySQL [bitnami_wordpress]> When I look at the indexes that already exist I see this: MySQL [bitnami_wordpress]> show indexes from wp_cron_logs; +--------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression | +--------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ | wp_cron_logs | 0 | PRIMARY | 1 | id | A | 180782 | NULL | NULL | | BTREE | | | YES | NULL | | wp_cron_logs | 1 | executed | 1 | executed | A | 53150 | NULL | NULL | YES | BTREE | | | YES | NULL | | wp_cron_logs | 1 | duration | 1 | duration | A | 54 | NULL | NULL | YES | BTREE | | | YES | NULL | +--------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ 3 rows in set (0.067 sec) MySQL [bitnami_wordpress]> Since there is an index on executed already, the problem is obviously a missing index on parent_id . I created one manually in my database like this: CREATE INDEX effecient_log_delete on wp_cron_logs(parent_id); And now we can see that the query is much much more performant: +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | -> Nested loop inner join (cost=7283.07 rows=12434) (actual time=0.244..31.770 rows=5723 loops=1) -> Filter: (wp_cron_logs.parent_id is null) (cost=2931.11 rows=1416) (actual time=0.226..13.515 rows=790 loops=1) -> Index range scan on wp_cron_logs using executed, with index condition: (wp_cron_logs.executed < <cache>(unix_timestamp((now() - interval 30 day)))) (cost=2931.11 rows=6513) (actual time=0.224..11.970 rows=6513 loops=1) -> Index lookup on wp_cron_logs using effecient_log_delete (parent_id=wp_cron_logs.id) (cost=2.20 rows=9) (actual time=0.004..0.019 rows=7 loops=790) | +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ Is it possible to include this update to the plugin itself to prevent performance issues in the DB? Creating this index costs very little and means that it’s one less database query that takes a long time to run. As you can see, New Relic showed a significant increase in the performance of the query, it has basically disappeared from the slow db queries list: View post on imgur.com This reply was modified 4 years, 5 months ago by robertsanair . This reply was modified 4 years, 5 months ago by robertsanair . Hi @robertsanair thanks for your suggestion. I’ve added the index optimization with version 1.1.1 of the plugin. Best regards Edward Thank you! That’s an awesome improvement. Now everybody everywhere gets the benefit of that performance improvement; I’ve installed the update on my staging site to test it out and it looks good.