-- Prepend EXPLAIN to any SELECT, INSERT, UPDATE, DELETE, or REPLACE
EXPLAIN SELECT u.name, COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE u.created_at > '2024-01-01'
GROUP BY u.id;

EXPLAIN FORMAT=JSON
SELECT * FROM orders WHERE customer_id = 42G

EXPLAIN ANALYZE
SELECT u.name, COUNT(o.id)
FROM users u
JOIN orders o ON o.user_id = u.id
GROUP BY u.idG

-- Before: no index on status column
EXPLAIN SELECT * FROM orders WHERE status = 'pending'G

-- Fix: add a composite index for this common query pattern
CREATE INDEX idx_orders_status_date ON orders (status, created_at);

-- After: re-run EXPLAIN
EXPLAIN SELECT * FROM orders
WHERE status = 'pending' AND created_at > '2024-01-01'G

-- Force use of a specific index
SELECT * FROM orders USE INDEX (idx_status)
WHERE status = 'pending';

-- Ignore a specific index (make optimizer choose differently)
SELECT * FROM orders IGNORE INDEX (idx_created_at)
WHERE status = 'pending';

-- MySQL 8.0 optimizer hints (preferred over USE INDEX)
SELECT /*+ INDEX(orders idx_status) */ *
FROM orders
WHERE status = 'pending';

-- Force a specific join order
SELECT /*+ JOIN_ORDER(u, o) */ u.name, o.total
FROM users u
JOIN orders o ON o.user_id = u.id;

-- Set max execution time to prevent runaway queries
SELECT /*+ MAX_EXECUTION_TIME(3000) */ *
FROM reports WHERE year = 2024;

-- Composite index: (user_id INT, status VARCHAR(20), created_at DATETIME)
-- INT = 4 bytes, VARCHAR(20) utf8mb4 = up to 20*4 + 2 = 82 bytes, DATETIME = 5 bytes

-- Query 1: WHERE user_id = 42
EXPLAIN SELECT * FROM orders WHERE user_id = 42G
-- key_len: 4   <-- only first column used (user_id INT = 4 bytes)

-- Query 2: WHERE user_id = 42 AND status = 'paid'
EXPLAIN SELECT * FROM orders WHERE user_id = 42 AND status = 'paid'G
-- key_len: 86  <-- first two columns used (4 + 82 = 86 bytes)

-- Query 3: WHERE user_id = 42 AND status = 'paid' ORDER BY created_at
EXPLAIN SELECT * FROM orders
WHERE user_id = 42 AND status = 'paid'
ORDER BY created_atG
-- key_len: 86  <-- still 86 (ORDER BY uses the index structure but not reflected in key_len)
-- Extra: NULL  <-- no filesort! ORDER BY is handled by index

-- EXPLAIN works on DML statements too
EXPLAIN UPDATE orders
SET status = 'shipped'
WHERE user_id = 42 AND status = 'paid';

-- EXPLAIN INSERT ... SELECT
EXPLAIN INSERT INTO order_archive
SELECT * FROM orders WHERE created_at < '2023-01-01';

-- EXPLAIN DELETE
EXPLAIN DELETE FROM sessions WHERE expires_at < NOW();

-- Query with multiple SELECT types
EXPLAIN
SELECT u.name,
       (SELECT COUNT(*) FROM orders WHERE user_id = u.id) AS order_count
FROM users u
WHERE u.id IN (SELECT user_id FROM vip_customers)G

-- Expected output:
-- id=1, select_type=PRIMARY: the outer SELECT on users
-- id=2, select_type=DEPENDENT SUBQUERY: the correlated COUNT subquery (runs once per user)
-- id=3, select_type=SUBQUERY: the IN (SELECT) on vip_customers (runs once)

-- Rewritten to avoid DEPENDENT SUBQUERY (correlated)
EXPLAIN
SELECT u.name, COUNT(o.id) AS order_count
FROM users u
JOIN vip_customers v ON v.user_id = u.id
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id, u.nameG
-- All rows now show SIMPLE or PRIMARY — no dependent subqueries

-- Create a partitioned table
CREATE TABLE orders_partitioned (
  id         INT AUTO_INCREMENT,
  created_at DATE NOT NULL,
  total      DECIMAL(10,2),
  PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (YEAR(created_at)) (
  PARTITION p2022 VALUES LESS THAN (2023),
  PARTITION p2023 VALUES LESS THAN (2024),
  PARTITION p2024 VALUES LESS THAN (2025),
  PARTITION future VALUES LESS THAN MAXVALUE
);

-- EXPLAIN shows which partitions are scanned
EXPLAIN SELECT * FROM orders_partitioned
WHERE created_at BETWEEN '2024-01-01' AND '2024-12-31'G
-- partitions: p2024   <-- only the 2024 partition is scanned (partition pruning)

-- Without partition pruning (all partitions scanned):
EXPLAIN SELECT * FROM orders_partitioned WHERE total > 100G
-- partitions: p2022,p2023,p2024,future

-- EXPLAIN shows derived tables as <derived2>, <derived3>, etc.
EXPLAIN
SELECT d.user_id, d.order_count, u.name
FROM (
  SELECT user_id, COUNT(*) AS order_count
  FROM orders
  GROUP BY user_id
  HAVING COUNT(*) > 5
) AS d
JOIN users u ON u.id = d.user_idG

-- Expected output includes a row for the derived table:
-- id=2, select_type=DERIVED, table=orders, type=ALL (subquery scans orders)
-- id=1, select_type=PRIMARY, table=<derived2>, type=ALL (result of subquery)
-- id=1, select_type=PRIMARY, table=u, type=eq_ref (join on PK)

-- In MySQL 8.0+, CTEs are materialized or merged:
EXPLAIN WITH top_users AS (
  SELECT user_id FROM orders GROUP BY user_id HAVING COUNT(*) > 10
)
SELECT u.name FROM users u WHERE u.id IN (SELECT user_id FROM top_users)G
-- select_type for CTE: MATERIALIZED or SUBQUERY

EXPLAIN FORMAT=JSON SELECT u.name, COUNT(o.id)
FROM users u JOIN orders o ON o.user_id = u.id
GROUP BY u.idG
-- Look for:
-- "query_cost": total estimated cost
-- "read_cost": cost to read rows from storage
-- "eval_cost": cost to evaluate conditions
-- Higher cost = more work = potentially slower

-- Compare two query variants by their query_cost values
-- The optimizer always chooses the lower-cost plan
-- If you disagree with the choice, check if statistics are stale (ANALYZE TABLE)