EXPLAIN in 5.6 works on UPDATE and DELETE too

The way to find out what a DELETE was about to do used to be to rewrite it as a SELECT with the same WHERE clause and explain that instead — which is close, and quietly wrong wherever the optimiser treats the two differently. 5.6 explains DML directly.

EXPLAIN DELETE FROM sessions
 WHERE last_seen < NOW() - INTERVAL 30 DAYG
-- *************************** 1. row ***************************
--          id: 1
-- select_type: DELETE
--       table: sessions
--        type: range
--         key: idx_last_seen
--        rows: 84120

EXPLAIN UPDATE orders SET status = 'archived'
 WHERE placed_at < '2013-01-01' AND status = 'complete'G

EXPLAIN FORMAT=JSON
SELECT * FROM orders WHERE customer_id = 91 ORDER BY placed_at DESCG

The practical use is the one-off maintenance statement — the archival UPDATE, the cleanup DELETE — where finding out afterwards means a table locked for half an hour. Reading type: ALL there is worth the ten seconds it costs. Two caveats. EXPLAIN on a DML statement takes the same metadata locks the statement itself would, so it can still block behind a long transaction despite changing nothing. And EXPLAIN FORMAT=JSON, also new in 5.6, is the more useful half of the feature for ordinary SELECTs: it reports the optimiser’s cost estimates and which parts of the WHERE clause were pushed down into the index, neither of which appears anywhere in the tabular output.