Lookups by action + instance can't use ts_action_instance_status, since that key leads with timestamp and these queries don't filter on it. They scan the whole table instead.
Both wp_clear_scheduled_hook() (via pre_clear_scheduled_hook(), limit => 500) and wp_next_scheduled() (limit => 1) produce that shape, and WP core calls the latter inside every wp_schedule_single_event().
Steps to reproduce
On a site with a large pending queue - ours had about 37,000 pending rows, and cost scales with that:
EXPLAIN ANALYZE
SELECT * FROM wp_a8c_cron_control_jobs
WHERE 1=1
AND action = 'publish_future_post'
AND instance = '<any 32 character instance hash>'
AND status IN ('pending','running')
ORDER BY timestamp ASC LIMIT 500;
instance doesn't need to match a real row - the scan happens either way.
-> Limit: 500 row(s) (actual time=98.3..98.3 rows=0 loops=1)
-> Index scan using ts_action_instance_status
(cost=119 rows=999) (actual time=0.0197..93.9 rows=38986 loops=1)
Every row read, none returned. The optimizer estimated 999 against an actual 38,986, which is why the plan looks cheap to it. An empty result is both the common case and the slowest, since LIMIT never gets to terminate early.
Stats from performance_schema
COUNT_STAR 1,022,213
AVG_TIMER_WAIT 1,219 ms
SUM_ROWS_EXAMINED 22,068,178,255
SUM_ROWS_SENT 323,229
About 21,000 rows examined per execution to return well under one row. The digest normalises LIMIT ?, so it covers both variants.
For contrast, lookups on the same table that do include timestamp run at 0.22 ms and 1 row examined, across 437,652 executions.
Why it surfaced hourly
The table is small - 38,034 rows, 607 complete, 37,428 pending - so this isn't bloat, it's pending count. These lookups are also object cached, so the scan doesn't normally reach response times.
The hourly a8c_cron_control_purge_completed_events run calls flush_event_cache() with no arguments, which bumps last_changed for both cache groups:
class-events-store.php:537 wp_cache_set( 'last_changed', microtime(), 'cron-control-queries' );
class-events-store.php:542 wp_cache_set( 'last_changed', microtime(), $cache_group ); // cron-control-event
Keys are built from wp_cache_get_last_changed(), so every cached lookup invalidates at once and they all hit the database together. Latency went from around 0.08s to 4.7s average with a 51s max in that window, and unrelated requests stalled behind it.
Side note: the narrower cron-control-event group exists to "avoid most bulk invalidations" per the comment at line 463, but the null-argument flush clears it anyway.
What we tested
ALTER TABLE wp_a8c_cron_control_jobs
ADD INDEX action_instance_status_ts (action(191), instance, status, timestamp);
38,986 rows read down to 0, and 98.3 ms down to 0.027 ms. The index doesn't stop the hourly invalidation, it just makes those cold lookups cheap - the affected window went from 620 transactions over 15s down to 3.
On the fix
DB_VERSION is written on install but never read back, and _prepare_table() only runs when the table is missing, so a schema change alone would only reach new installs. I've opened a PR that adds the index plus a version check - happy to adjust if you'd rather handle the rollout another way:
Filing rather than just keeping our local index, since it sits outside the plugin's schema versioning and a future update could drop it.
Tracking: PLTFRM-2722
Lookups by
action+instancecan't usets_action_instance_status, since that key leads withtimestampand these queries don't filter on it. They scan the whole table instead.Both wp_clear_scheduled_hook() (via
pre_clear_scheduled_hook(),limit => 500) and wp_next_scheduled() (limit => 1) produce that shape, and WP core calls the latter inside every wp_schedule_single_event().Steps to reproduce
On a site with a large pending queue - ours had about 37,000 pending rows, and cost scales with that:
instancedoesn't need to match a real row - the scan happens either way.Every row read, none returned. The optimizer estimated 999 against an actual 38,986, which is why the plan looks cheap to it. An empty result is both the common case and the slowest, since
LIMITnever gets to terminate early.Stats from
performance_schemaAbout 21,000 rows examined per execution to return well under one row. The digest normalises
LIMIT ?, so it covers both variants.For contrast, lookups on the same table that do include
timestamprun at 0.22 ms and 1 row examined, across 437,652 executions.Why it surfaced hourly
The table is small - 38,034 rows, 607 complete, 37,428 pending - so this isn't bloat, it's pending count. These lookups are also object cached, so the scan doesn't normally reach response times.
The hourly
a8c_cron_control_purge_completed_eventsrun callsflush_event_cache()with no arguments, which bumpslast_changedfor both cache groups:Keys are built from
wp_cache_get_last_changed(), so every cached lookup invalidates at once and they all hit the database together. Latency went from around 0.08s to 4.7s average with a 51s max in that window, and unrelated requests stalled behind it.Side note: the narrower
cron-control-eventgroup exists to "avoid most bulk invalidations" per the comment at line 463, but the null-argument flush clears it anyway.What we tested
38,986 rows read down to 0, and 98.3 ms down to 0.027 ms. The index doesn't stop the hourly invalidation, it just makes those cold lookups cheap - the affected window went from 620 transactions over 15s down to 3.
On the fix
DB_VERSIONis written on install but never read back, and_prepare_table()only runs when the table is missing, so a schema change alone would only reach new installs. I've opened a PR that adds the index plus a version check - happy to adjust if you'd rather handle the rollout another way:Filing rather than just keeping our local index, since it sits outside the plugin's schema versioning and a future update could drop it.
Tracking: PLTFRM-2722