EXPLAIN describes the estimated plan; EXPLAIN ANALYZE executes the statement and measures its iterators. Use a read-only example to compare estimated and actual rows, repeated loops and a deliberately ordered composite index.
Scope and prerequisites
Use a MySQL 8.x version that supports EXPLAIN ANALYZE; do not assume MariaDB is identical. orders and its columns are illustrative. Begin on a representative staging instance or replica. ANALYZE actually executes, making write statements and heavy production queries risky. Prepare a session time limit and cancellation access.
1. Collect evidence
Capture the actual query, parameters, cardinality and existing indexes. TREE reports actual time in milliseconds and averages it per loop when loops repeat. Parent times include child work, so summing all nodes double-counts. Large estimated-versus-actual row differences justify investigating statistics and data skew.
SHOW INDEX FROM orders;
EXPLAIN FORMAT=TREE
SELECT id, created_at FROM orders
WHERE customer_id=42 ORDER BY created_at DESC LIMIT 20;
2. Make a controlled change
For equality on customer_id and ordering by created_at, the composite index below is a plausible candidate, not a guarantee. Before DDL, assess disk space, metadata locking, write overhead and version-specific online behavior. Avoid redundant indexes and derive column order from actual equality, range and sort patterns.
-- Staging example: inspect existing indexes before DDL.
CREATE INDEX ix_orders_customer_created ON orders(customer_id, created_at);
3. Verify the outcome
Compare plans on the same data and parameter set. Fewer examined rows alone do not establish success; cache state and concurrency affect latency. Results must remain identical. A full scan can be appropriate for a small table, and an index name in the plan is not by itself a performance win.
SET SESSION max_execution_time=5000;
EXPLAIN ANALYZE FORMAT=TREE
SELECT id, created_at FROM orders
WHERE customer_id=42 ORDER BY created_at DESC LIMIT 20;
Rollback and prevention
If the new index increases write cost or replica lag, plan removal of that specific index in a controlled window. Schema changes also need tested rollback. Monitor latency and examined rows by query digest. More RAM or indexing every column cannot replace understanding the execution plan.
Technical references
For related checks in this workflow, see MySQL Point-in-Time Recovery with Binary Logs and Choose Redis maxmemory and Eviction Policies Safely.
