Why might this query be running very slow? EXPLAIN DELETE d FROM xf_zee_botguard_event_detail AS d INNER JOIN ( SELECT event_id FROM ( SELECT d2.event_id FROM xf_zee_botguard_event_detail AS d2 LEFT JOIN xf_zee_botguard_event AS e ON e.event_id = d2.event_id WHERE e.event_id IS NULL ORDER BY d2.event_id LIMIT 1000 ) AS pending_event_detail_orphans ) AS doomed ON doomed.event_id = d.event_id: *** row 1 *** table: <derived2> type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 1000 Extra: NULL *** row 2 *** table: d type: eq_ref possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: doomed.event_id rows: 1 Extra: NULL *** row 3 *** table: <derived3> type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 1000 Extra: NULL *** row 4 *** table: d2 type: index possible_keys: NULL key: PRIMARY key_len: 4 ref: NULL rows: 13569290 Extra: Using index *** row 5 *** table: e type: eq_ref possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: forum_xen.d2.event_id rows: 1 Extra: Using where; Using index; Not exists
Show more
Diagnosed quadratic complexity from repeated full table scans
Diagnosed quadratic complexity from repeated full table scans
A few things stack up here, but the dominant one is row 4:
table: d2 type: index key: PRIMARY rows: 13569290 Extra: Using index
That's a full scan of the detail table's clustered index — 13.5M rows — with an eq_ref probe into xf_zee_botguard_event for each one. The LIMIT 1000 doesn't bound that scan; it only stops it once 1000 rows have survived the e.event_id IS NULL filter. If orphans are rare, or clustered toward the high end of event_id, MySQL grinds through millions of rows and millions of random PK lookups before it collects its thousand.
The killer case is the tail end: once fewer than 1000 orphans remain, the query must read all 13.5M rows to prove it. Every "empty" run costs a full table scan.
If you're running this in a loop, it's quadratic. Each iteration restarts from the lowest event_id and re-walks all the non-orphan rows it already rejected last time. 13,569 batches × an ever-growing prefix scan is the real reason it feels like it never finishes.
Some secondary contributors:
The <derived2> / <derived3> double nesting is materialized into temp tables. You need one level (MySQL error 1093 — can't read the target table in a subquery of a DELETE), but the outer wrapper is redundant work.
13.5M random lookups into e's primary key is I/O-bound unless both clustered indexes fit in the buffer pool. Worth checking innodb_buffer_pool_size against the size of the two tables.
Any secondary indexes on the detail table make each row delete more expensive, though that's minor next to the scan.
The ORDER BY d2.event_id is actually free — it matches PK order, so no filesort.
The fix: range chunks instead of LIMIT
Multi-table DELETE doesn't support LIMIT, which is presumably why you built the derived-table scaffolding. But it does support a WHERE clause, so walk the key space instead:
sql
DELETE d
FROM xf_zee_botguard_event_detail AS d
LEFT JOIN xf_zee_botguard_event AS e ON e.event_id = d.event_id
WHERE e.event_id IS NULL
AND d.event_id >= ? AND d.event_id < ?;
Drive ? from a cursor you advance by, say, 50,000 IDs per iteration. Each batch touches a bounded range of the clustered index, no derived tables, no re-scanning, and the total work across the whole job is a single pass over the table.
If you'd rather keep the LIMIT shape, the minimum change is to add AND d2.event_id > ? seeded with the highest event_id examined in the previous batch — not the highest deleted. That alone converts the O(n²) loop into O(n).
Third option if orphans are sparse: pay for one full scan up front, dump the orphan IDs into a work table, then delete in batches joined against that. One scan total instead of one per batch.
