Query Optimisation — Cheat Sheet

EXPLAIN shows the execution plan: type (const, eq_ref, ref, range, ALL), key used, rows scanned, and Extra flags like Using filesort, Using temporary — key inputs for tuning.

• ✓`type: ALL` means a full table scan — always investigate; add an index or rewrite the query.
• ✓Access type ladder (best → worst): const → eq_ref → ref → range → index → ALL.
• ✓`Using filesort` and `Using temporary` in Extra are red flags for missing indexes on ORDER BY / GROUP BY columns.
• ✓A composite index (a, b) covers `WHERE a = ? ORDER BY b` — no filesort needed if column order matches.
• ✓EXPLAIN ANALYZE (MySQL 8.0+) reveals actual vs estimated row counts — use it when estimates look wrong.
• ✓Run `ANALYZE TABLE` after bulk inserts to refresh the InnoDB statistics used by the optimiser.

-- Sample schema
CREATE TABLE orders (
  id         INT PRIMARY KEY AUTO_INCREMENT,
  user_id    INT NOT NULL,
  status     VARCHAR(20),
  created_at DATETIME,
  INDEX idx_user (user_id),
  INDEX idx_status_created (status, created_at)
);

-- 1. Full table scan — no usable index on 'amount'
EXPLAIN SELECT * FROM orders WHERE amount > 100;
-- type: ALL, key: NULL, rows: ~50000, Extra: Using where

-- 2. Range scan — leftmost prefix of composite index
EXPLAIN SELECT * FROM orders WHERE status = 'PENDING' AND created_at > '2024-01-01';
-- type: range, key: idx_status_created, rows: ~200, Extra: Using index condition

-- 3. eq_ref in a JOIN — primary key lookup
EXPLAIN
  SELECT o.id, u.email
  FROM orders o
  JOIN users u ON u.id = o.user_id
  WHERE o.status = 'PENDING';
-- orders: type: ref, key: idx_status_created
-- users:  type: eq_ref, key: PRIMARY

Add indexes, rewrite correlated subqueries as JOINs, avoid SELECT *, use LIMIT, avoid functions on indexed columns in WHERE, and leverage the query cache (or ProxySQL) carefully.

• ✓Wrap an indexed column in a function (YEAR(), LOWER(), etc.) in a WHERE clause and the index cannot be used — rewrite as a range.
• ✓Correlated subqueries re-execute per outer row; rewrite as a JOIN with a pre-aggregated subquery for O(1) vs O(n) performance.
• ✓SELECT * prevents covering-index optimisation and fetches unnecessary data — always select only needed columns.
• ✓LIMIT/OFFSET pagination degrades at high offsets; use keyset (cursor) pagination with WHERE id > last_seen_id for consistent performance.
• ✓Implicit type conversions (string literal on INT column) silently disable indexes — always match parameter types to column types.
• ✓Use EXPLAIN ANALYZE after every optimisation to confirm the plan changed as expected and estimate improvements.

-- ❌ BAD: function on indexed column — full scan
SELECT * FROM orders WHERE YEAR(created_at) = 2024;
-- EXPLAIN type: ALL (no index)

-- ✅ GOOD: rewrite as range — index used
SELECT id, total FROM orders
WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01';
-- EXPLAIN type: range, key: idx_created_at

-- ❌ BAD: implicit type conversion on user_id INT column
SELECT * FROM users WHERE user_id = '456';  -- string vs INT → full scan

-- ✅ GOOD: matching type
SELECT id, email FROM users WHERE user_id = 456;

-- ❌ BAD: SELECT * over wide table
SELECT * FROM products WHERE category_id = 5;

-- ✅ GOOD: select only needed columns — can use covering index
SELECT id, name, price FROM products WHERE category_id = 5;

Enable slow_query_log with a long_query_time threshold to capture expensive queries; use mysqldumpslow or pt-query-digest to identify and rank the worst offenders.

• ✓Enable slow_query_log at runtime with SET GLOBAL — no MySQL restart needed.
• ✓Start with long_query_time=1 in production; lower to 0.1–0.5 once you've fixed the worst offenders.
• ✓log_queries_not_using_indexes captures harmful full-table scans even when they are fast on small datasets.
• ✓Use pt-query-digest (not mysqldumpslow) for production analysis — it gives full statistics, percentiles, and can output to a report table.
• ✓Sort by total time (not average) to identify the highest-impact queries — a 0.1s query called 100 000 times matters more than a 5s query called once.
• ✓The slow log is discovery; EXPLAIN is diagnosis; adding indexes/rewriting is the fix.

-- Enable at runtime (no restart needed)
SET GLOBAL slow_query_log      = 'ON';
SET GLOBAL long_query_time     = 1;      -- log queries taking > 1 second
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';
SET GLOBAL log_queries_not_using_indexes = 'ON';  -- also catch full-table scans

-- Verify settings
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';

-- Persist in my.cnf (survives restart)
[mysqld]
slow_query_log          = ON
slow_query_log_file     = /var/log/mysql/slow.log
long_query_time         = 0.5      # 500ms threshold for busy production apps
log_queries_not_using_indexes = ON
log_throttle_queries_not_using_indexes = 10  # limit to 10 per minute (avoids log flood)

-- Check how many slow queries have been captured
SHOW GLOBAL STATUS LIKE 'Slow_queries';