EXPLAIN

-- 1) Basic usage
EXPLAIN SELECT * FROM orders WHERE customer_id = 42;

-- Columns to know:
--   id            execution step (subquery / union ordering)
--   select_type   SIMPLE / PRIMARY / SUBQUERY / DERIVED / UNION
--   table          table being read
--   partitions    partition list used
--   type          access method (system / const / eq_ref / ref / range / index / ALL)
--                   const/eq_ref FAST   .. ALL SLOW (full scan)
--   possible_keys indexes the optimiser could use
--   key           index actually chosen (NULL = none)
--   key_len        bytes of the chosen index used
--   ref            what is compared to the index (const / column)
--   rows           estimated rows examined
--   filtered       % of those rows that match WHERE (rough; combined with rows)
--   Extra          notes (Using index / Using where / Using temporary / Using filesort)

-- 2) EXPLAIN FORMAT=JSON — full plan tree, much more detail
EXPLAIN FORMAT=JSON SELECT * FROM orders WHERE customer_id = 42\G

-- 3) EXPLAIN ANALYZE — actually RUNS the query + reports real timings (MySQL 8.0.18+)
EXPLAIN ANALYZE SELECT o.id, o.total_cents, u.email
FROM orders o
JOIN users u ON u.id = o.customer_id
WHERE o.created_at >= NOW() - INTERVAL 30 DAY
ORDER BY o.created_at DESC
LIMIT 100;
--
-- -> Limit: 100 row(s)  (cost=1.05 rows=100) (actual time=0.32..1.21 rows=100 loops=1)
--     -> Nested loop inner join  (cost=86.4 rows=864) (actual time=0.31..1.15 rows=100 loops=1)
--         -> Index range scan on o using idx_orders_created  (rows=864)
--             (actual time=0.04..0.42 rows=100 loops=1)
--         -> Single-row index lookup on u using PRIMARY  (uid=o.customer_id)
--             (cost=0.07 rows=1) (actual time=0.007..0.007 rows=1 loops=100)

-- 4) Reading the 'type' column
-- const          — single row from a unique index = primary key. Fastest.
-- eq_ref         — for each row in the previous table, one row from this one (PK / unique). Fast.
-- ref            — non-unique index lookup; reads matching rows. Good if rows small.
-- range          — index range scan (WHERE col BETWEEN, IN, > <)
-- index          — full INDEX scan (better than ALL but still reads all rows in the index)
-- ALL            — full TABLE scan. Bad on big tables; usually means missing index.

-- 5) Extra column highlights
-- Using index            — covering index; reads everything from the index, no table lookup
-- Using where            — filter applied after row read
-- Using temporary        — temp table needed (DISTINCT / GROUP BY / sort) — fix with index
-- Using filesort         — sort happens in memory or on disk; usually OK on small results
-- Using join buffer      — block-nested-loop join (no usable index on the join column) — add index
-- Impossible WHERE        — query returns nothing (constant evaluation said so)
-- Select tables optimised away — aggregate fully answered from indexes

-- 6) Adding the right index — the cardinal example
EXPLAIN SELECT * FROM orders WHERE status = 'paid' ORDER BY created_at DESC LIMIT 50;
-- Without index: type=ALL, rows=2_000_000, Extra=Using where; Using filesort

CREATE INDEX idx_orders_status_created ON orders (status, created_at DESC);
-- Now: type=ref, rows=50, Extra=Using index condition (no filesort)
--
-- The order of columns matters:
--   • Equality columns first (status = 'paid')
--   • Then ordering / range column (created_at)
--   • Add covered columns last if you want a covering index

-- 7) Covering indexes — read everything from the index
CREATE INDEX idx_orders_cover ON orders (status, created_at DESC, id, total_cents);
EXPLAIN SELECT id, total_cents FROM orders WHERE status='paid' ORDER BY created_at DESC LIMIT 10;
-- Extra: Using index — never touches the actual table data; fastest possible.

-- 8) Index merge — multiple indexes combined
-- The optimiser sometimes intersects or unions indexes when one wouldn't suffice.
-- Often a sign you need a single multi-column index instead.
EXPLAIN SELECT * FROM users WHERE name='Mara' OR email='m@example.com';
-- key: idx_name, idx_email   Extra: Using union(idx_name, idx_email)

-- 9) Joins — index the join column
EXPLAIN
SELECT o.id, u.email
FROM orders o JOIN users u ON u.id = o.customer_id
WHERE o.status='paid';
-- For each row in orders, MySQL looks up users by PK (eq_ref). Good.
-- If users.id were not unique → ref + many rows per outer row → expensive.

-- 10) Subqueries vs joins
EXPLAIN SELECT id FROM orders
WHERE customer_id IN (SELECT id FROM users WHERE country='AU');
-- Modern MySQL often rewrites as semi-join (good). Old plans materialise temp tables (bad).

-- 11) When the optimiser picks the 'wrong' index
SELECT * FROM orders USE  INDEX (idx_orders_status_created) WHERE status='paid';
SELECT * FROM orders FORCE INDEX (idx_orders_status_created) WHERE status='paid';
SELECT * FROM orders IGNORE INDEX (idx_orders_status) WHERE status='paid';
-- Use sparingly; prefer FIXING statistics with ANALYZE TABLE or rewriting the query.

-- 12) Statistics + ANALYZE
ANALYZE TABLE orders;
-- Re-samples histograms / cardinality used by the planner.
-- Run after big data changes (bulk load, large delete, partition change).

-- 13) Slow query log
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1.0;
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';
-- Pair with mysqldumpslow / pt-query-digest for top offenders.

-- 14) Optimiser hints (MySQL 8+)
SELECT /*+ INDEX(orders idx_orders_status_created) */ id FROM orders WHERE status='paid';
SELECT /*+ MAX_EXECUTION_TIME(2000) */ /* statement-level timeout in ms */ id FROM orders;
SELECT /*+ NO_BNL(o, u) */ ... ;  -- disable block-nested-loop for these tables

-- 15) Common bugs
-- • Index never used → leading column not in WHERE (multi-col index requires left-prefix)
-- • Type=ALL with a small WHERE → missing index; check possible_keys
-- • Using filesort on a paginated query → add an index that already orders by the sort column
-- • Functions on indexed columns (WHERE DATE(created_at) = '2024-01-01') → index unusable; rewrite as range
-- • Implicit cast (WHERE phone = 12345 but phone is VARCHAR) → index unusable; quote the value
-- • Stale statistics — ANALYZE TABLE after big data changes
-- • Covering index NOT covering — SELECT * read fields not in index; trim to SELECT a, b
-- • EXPLAIN result misleading on a query that doesn't actually run — use EXPLAIN ANALYZE
-- • Plan changes after a server restart or version upgrade — rebuild histograms / pin hints

EXPLAIN SELECT * FROM orders WHERE user_id = 42 AND status = 'paid';
-- Check: type = ref / index, key = used index, rows = scanned