MySQL Query Optimization

Index design is only the first step. Even with perfect indexes, a poorly written SQL query can still cripple your database. This chapter starts with the query execution pipeline internals, then dissects JOIN algorithms, subquery transformations, pagination bottlenecks, ORDER BY/GROUP BY optimization, bulk DML strategies, and concludes with three real-world case studies and a 20-item anti-pattern checklist. Every topic includes EXPLAIN output and quantifiable performance comparisons.

1. Query Execution Pipeline

1.1 The Full Path from SQL Text to Result Set

When a SQL statement arrives at the MySQL Server, it passes through the following stages:

1.2 Parser Stage

MySQL uses Bison (a YACC variant) to define SQL grammar rules (sql/sql_yacc.yy, over 17,000 lines). The Parser converts SQL text into a Parse Tree (internal structures SELECT_LEX / Query_block).

This stage only checks whether syntax is valid, not whether tables or columns exist. For example, SELECT * FORM users fails at the Parser stage (FORM is not a keyword), but SELECT * FROM nonexistent_table passes through.

1.3 Preprocessor Stage

The Preprocessor (also called Resolver) performs semantic analysis:

• Name resolution: Verifying table, column, and function existence; resolving alias references
• Permission checks: Whether the current user has SELECT/UPDATE privileges
• Type inference: Determining expression return types; inserting implicit type conversions
• Wildcard expansion: Replacing SELECT * with the actual column list
• View expansion: Merging view definitions into the query tree (MERGE algorithm) or creating derived tables (TEMPTABLE algorithm)

1.4 Optimizer Stage

The optimizer is MySQL's most complex module, divided into two sub-stages:

Logical Optimization (RBO Rules)

• Constant folding: WHERE 1=1 AND id > 5 → WHERE id > 5
• Predicate pushdown: Applying WHERE conditions as early as possible to the innermost table
• Subquery transformation: IN subquery → semi-join; NOT IN → anti-join
• Outer join elimination: Automatically converting LEFT JOIN to INNER JOIN when WHERE conditions make them equivalent
• Redundant sort elimination: Skipping filesort when ORDER BY matches index order
• Condition simplification: WHERE a > 5 AND a > 3 → WHERE a > 5

Physical Optimization (CBO Cost Model)

MySQL 8.0 uses a cost model to evaluate different execution plan costs. The cost consists of two components:

The optimizer's core decisions include:

1. Access path selection: Full table scan vs index scan vs index range scan vs ref access
2. JOIN ordering: For N tables, there are N! possible permutations. When N ≤ optimizer_search_depth (default 62, practically exhaustive for 7-8 tables), it does exhaustive search; otherwise uses greedy heuristics
3. JOIN algorithm: NLJ / BNL / Hash Join / BKA+MRR
4. Subquery strategy: Materialization, FirstMatch, LooseScan, etc.

1.5 Executor Stage (Iterator Model)

MySQL 8.0 refactored the traditional handler-call approach into the Iterator Model:

Each Iterator implements Init() and Read() methods. The upper Iterator calls the lower one's Read() to pull one row, processes it, and returns it upward — this is the classic "Volcano Model." EXPLAIN ANALYZE outputs execution statistics for this iterator tree.

2. JOIN Algorithms Deep Dive

2.1 Nested Loop Join (NLJ)

The Nested Loop Join is MySQL's most fundamental JOIN algorithm and the preferred algorithm when indexes are available:

-- Pseudocode: Nested Loop Join
for each row r1 in outer_table:          -- Driving table (outer loop)
    for each row r2 in inner_table:      -- Driven table (inner loop)
        if r1.join_key == r2.join_key:   -- Via index lookup
            output (r1, r2)

Key points:

• The driving table is scanned fully (or via index range scan). For each row, an index lookup finds matching rows in the driven table
• If the driven table has an index on the JOIN column, each lookup is O(log n), total cost is O(M * log N)
• The optimizer chooses the table with the smaller result set as the driving table ("small table drives big table" principle)
• When the driven table has a suitable index, NLJ is highly efficient

2.2 Block Nested Loop (BNL)

When the driven table has no index on the JOIN column, each inner loop iteration requires a full table scan of the driven table. With M rows in the driving table and N rows in the driven table, this means scanning the driven table M times — catastrophic I/O.

BNL optimizes this by loading driving table rows in batches into a JOIN Buffer (memory), then scanning the driven table once per batch:

-- Pseudocode: Block Nested Loop
while join_buffer is not full:
    read rows from outer_table into join_buffer

    for each row r2 in inner_table:       -- Full scan of driven table
        for each row r1 in join_buffer:   -- Compare in memory
            if r1.join_key == r2.join_key:
                output (r1, r2)

    clear join_buffer

2.3 Hash Join (MySQL 8.0.18+)

Hash Join was a revolutionary feature introduced in MySQL 8.0.18, specifically designed to solve performance issues with non-indexed equi-JOINs:

-- Pseudocode: Hash Join
-- 1. Build Phase
hash_table = {}
for each row r1 in smaller_table:       -- Choose the smaller table
    hash_table[hash(r1.join_key)] = r1  -- Build hash table

-- 2. Probe Phase
for each row r2 in larger_table:        -- Scan the larger table
    if hash(r2.join_key) in hash_table: -- O(1) lookup
        for each r1 in hash_table[hash(r2.join_key)]:
            output (r1, r2)

Hash Join memory management:

• Hash table size is limited by join_buffer_size
• When the hash table exceeds memory, MySQL spills to disk (temporary files)
• Spill strategy: Grace Hash Join — partition both build and probe inputs by hash, write to disk, then process partition by partition
• MySQL 8.0.18 initially only supported in-memory Hash Join; disk spill support was added in 8.0.20

Hash Join trigger conditions (MySQL 8.0.18+):

1. Equi-join condition (a.id = b.id) — 8.0.20+ also supports non-equi conditions and Cartesian products
2. No usable index on the driven table's JOIN column (otherwise optimizer prefers NLJ)
3. block_nested_loop=on in optimizer_switch (default on)

2.4 Batched Key Access (BKA) + Multi-Range Read (MRR)

BKA enhances NLJ by solving the random I/O problem. Standard NLJ performs random index lookups on the driven table. BKA batches and sorts the keys to convert random reads into sequential reads:

-- BKA is off by default; enable manually
SET optimizer_switch = 'mrr=on,mrr_cost_based=off,batched_key_access=on';

-- Or use hints for a single query:
SELECT /*+ BKA(t2) MRR(t2) */ *
FROM orders t1 JOIN users t2 ON t1.user_id = t2.id;

2.5 JOIN Algorithm Performance Comparison

3. Subquery Optimization

3.1 Correlated vs Non-Correlated Subqueries

-- Non-correlated: executed once
SELECT * FROM orders
WHERE user_id IN (SELECT id FROM users WHERE country = 'US');

-- Correlated: executed once per outer row
SELECT *, (SELECT name FROM users u WHERE u.id = o.user_id) AS user_name
FROM orders o;

3.2 Subquery Materialization

The optimizer can materialize a subquery's result set into a temporary table, then use that temporary table for the JOIN:

-- Original query
SELECT * FROM orders WHERE user_id IN (SELECT id FROM users WHERE vip = 1);

-- Optimizer may internally execute as:
-- 1. Execute subquery, store results in temp table (with dedup + hash index)
-- 2. SELECT * FROM orders WHERE user_id IN (SELECT id FROM materialized_temp)
-- EXPLAIN shows: MATERIALIZED

3.3 Semi-Join Transformations

Semi-join (MySQL 5.6+) is a critical optimization. When IN or EXISTS subqueries qualify, the optimizer converts them to semi-joins, enabling more efficient JOIN algorithms. Semi-join semantics: for each outer row, return it if at least one inner row matches.

MySQL implements four semi-join strategies:

SELECT * FROM employees e
WHERE e.dept_id IN (SELECT d.id FROM departments d WHERE d.budget > 1000000);
-- EXPLAIN: FirstMatch(e)

SELECT * FROM departments d
WHERE d.id IN (SELECT dept_id FROM employees WHERE salary > 100000);
-- With idx(dept_id) on employees, LooseScan jumps between distinct values

-- EXPLAIN: Start temporary / End temporary

3.4 EXISTS vs IN vs JOIN Comparison

-- Approach 1: IN subquery
SELECT * FROM orders o
WHERE o.user_id IN (SELECT u.id FROM users u WHERE u.country = 'US');

-- Approach 2: EXISTS
SELECT * FROM orders o
WHERE EXISTS (SELECT 1 FROM users u WHERE u.id = o.user_id AND u.country = 'US');

-- Approach 3: JOIN
SELECT o.* FROM orders o
INNER JOIN users u ON o.user_id = u.id WHERE u.country = 'US';

-- MySQL 8.0 optimizer typically transforms all three into the same plan (semi-join)
-- In MySQL 5.7 and earlier, IN subquery may not be optimized

3.5 Anti-Join Patterns (NOT EXISTS / NOT IN / LEFT JOIN WHERE NULL)

-- Goal: "Find users who have never placed an order"

-- Approach 1: NOT EXISTS (recommended)
SELECT * FROM users u
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

-- Approach 2: NOT IN (dangerous if orders.user_id has NULLs!)
SELECT * FROM users u WHERE u.id NOT IN (SELECT user_id FROM orders);

-- Approach 3: LEFT JOIN + WHERE NULL
SELECT u.* FROM users u
LEFT JOIN orders o ON u.id = o.user_id WHERE o.id IS NULL;

3.6 Subquery Rewriting Examples

-- BEFORE: One subquery per row (100K rows = 100K subqueries)
SELECT o.id, o.amount,
    (SELECT u.name FROM users u WHERE u.id = o.user_id) AS user_name
FROM orders o;

-- AFTER: Single JOIN
SELECT o.id, o.amount, u.name AS user_name
FROM orders o LEFT JOIN users u ON u.id = o.user_id;
-- Improvement: 800ms → 45ms

-- BEFORE
SELECT e.*, e.salary / (SELECT SUM(salary) FROM employees e2
    WHERE e2.dept_id = e.dept_id) AS pct
FROM employees e;

-- AFTER (MySQL 8.0+)
SELECT e.*, e.salary / SUM(e.salary) OVER (PARTITION BY e.dept_id) AS pct
FROM employees e;
-- From N subqueries → single scan, 10-50x improvement

-- BEFORE: 3 full table scans
SELECT
    (SELECT COUNT(*) FROM orders WHERE status = 'pending') AS pending,
    (SELECT COUNT(*) FROM orders WHERE status = 'shipped') AS shipped,
    (SELECT COUNT(*) FROM orders WHERE status = 'delivered') AS delivered;

-- AFTER: Single scan
SELECT
    COUNT(CASE WHEN status = 'pending' THEN 1 END) AS pending,
    COUNT(CASE WHEN status = 'shipped' THEN 1 END) AS shipped,
    COUNT(CASE WHEN status = 'delivered' THEN 1 END) AS delivered
FROM orders;

-- BEFORE: Nested derived tables, hard to read
SELECT * FROM (
    SELECT user_id, SUM(amount) AS total FROM orders GROUP BY user_id
) AS t1 JOIN (
    SELECT user_id, COUNT(*) AS cnt FROM returns GROUP BY user_id
) AS t2 ON t1.user_id = t2.user_id;

-- AFTER: CTE, clearer and reusable
WITH order_totals AS (
    SELECT user_id, SUM(amount) AS total FROM orders GROUP BY user_id
),
return_counts AS (
    SELECT user_id, COUNT(*) AS cnt FROM returns GROUP BY user_id
)
SELECT * FROM order_totals JOIN return_counts
ON order_totals.user_id = return_counts.user_id;

-- BEFORE
SELECT e.*, (SELECT COUNT(*) FROM employees e2
    WHERE e2.dept_id = e.dept_id AND e2.salary > e.salary) + 1 AS salary_rank
FROM employees e;

-- AFTER
SELECT e.*, RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS salary_rank
FROM employees e;

4. Pagination Optimization

4.1 The LIMIT Large Offset Problem

Pagination queries are among the most common in web applications. But the traditional LIMIT offset, count becomes a severe performance bottleneck at large offsets:

-- Page 1: fast
SELECT * FROM articles ORDER BY created_at DESC LIMIT 0, 20;
-- Scans 20 rows, returns 20 rows

-- Page 5000: extremely slow!
SELECT * FROM articles ORDER BY created_at DESC LIMIT 100000, 20;
-- MySQL must scan (and sort) 100,020 rows, discard the first 100,000, return only 20
-- Those 100,000 rows of scanning work are completely wasted

4.2 Solution 1: Deferred JOIN

Core idea: First retrieve only primary key IDs via a covering index, then JOIN back for full row data.

-- Original query (slow)
SELECT * FROM articles ORDER BY created_at DESC LIMIT 100000, 20;

-- Deferred JOIN (fast)
SELECT a.* FROM articles a
INNER JOIN (
    SELECT id FROM articles ORDER BY created_at DESC LIMIT 100000, 20
) AS tmp ON a.id = tmp.id;

-- Why faster?
-- The subquery scans only index pages (much smaller than data pages)
-- Scanning 100K index entries is much faster than 100K full rows
-- Finally only 20 IDs need to go back to the table for full data

4.3 Solution 2: Cursor-based Pagination (Keyset Pagination)

Cursor-based pagination completely avoids OFFSET by remembering the position of the last record on the previous page:

-- Page 1
SELECT * FROM articles ORDER BY created_at DESC, id DESC LIMIT 20;
-- Last row: created_at = '2024-03-15 10:30:00', id = 98765

-- Page 2 (using last row's values as cursor)
SELECT * FROM articles
WHERE (created_at, id) < ('2024-03-15 10:30:00', 98765)
ORDER BY created_at DESC, id DESC
LIMIT 20;

Advantages: Constant performance regardless of page depth; not affected by concurrent inserts/deletes; ideal for infinite scroll.

Disadvantages: Cannot jump directly to page N; frontend must pass cursor parameters; sort fields must be indexed and unique with ID.

4.4 Solution 3: Covering Index for Sort + Filter

-- If you only need a few columns
SELECT id, title, created_at FROM articles
ORDER BY created_at DESC LIMIT 100000, 20;

-- Create covering index
ALTER TABLE articles ADD INDEX idx_cover (created_at DESC, id, title);
-- Entire query completed within the index, no table access
-- EXPLAIN: Using index

4.5 Pagination Performance Comparison

5. ORDER BY and GROUP BY Optimization

5.1 Filesort Algorithms

When MySQL cannot obtain sorted results directly from an index, it performs filesort. Despite the name, filesort primarily occurs in memory, spilling to disk only when data exceeds sort_buffer_size.

5.2 sort_buffer_size Tuning

• Default: 256KB (MySQL 8.0)
• Spill behavior: When sort data exceeds sort_buffer_size, MySQL uses merge sort — splits data into sorted runs on disk, then merges
• Detecting spills: Check SHOW STATUS LIKE 'Sort_merge_passes'; growing values indicate buffer is too small
• Recommendation: Don't set too large globally (per-thread allocation). 1-8MB is reasonable. Use session-level settings for specific large sorts

5.3 Using Indexes to Avoid Filesort

The best sort optimization is to avoid sorting entirely. If ORDER BY columns match index order, MySQL reads from the index in order:

-- Index: INDEX idx_created (created_at)

-- Can use index for sorting (no filesort)
SELECT * FROM articles WHERE status = 'published'
ORDER BY created_at DESC LIMIT 20;

-- Mixed ASC/DESC with composite index
-- INDEX idx_ab (a, b)
-- ORDER BY a, b         → uses index ✓
-- ORDER BY a DESC, b DESC → uses index ✓ (backward scan)
-- ORDER BY a ASC, b DESC  → 5.7: no, 8.0: yes (requires DESC index)
-- WHERE a = 1 ORDER BY b  → uses index ✓ (a is equality, b is sort)

5.4 Loose Index Scan for GROUP BY

-- INDEX idx_dept_salary (dept_id, salary)

-- Find max salary per department
SELECT dept_id, MAX(salary) FROM employees GROUP BY dept_id;

-- Loose Index Scan:
-- 1. Jump to first record of dept_id=1
-- 2. Since index is sorted by (dept_id, salary), last entry = max
-- 3. Jump to dept_id=2 boundary, take last entry
-- Only visits boundary rows, skips all middle rows per group
-- EXPLAIN: Using index for group-by

5.5 ORDER BY + LIMIT Optimization

When ORDER BY appears with LIMIT, MySQL uses a priority queue (heap sort) instead of a full sort:

-- Only need top 10
SELECT * FROM articles ORDER BY score DESC LIMIT 10;

-- Optimizer uses priority queue (min/max heap):
-- Maintains a heap of size 10
-- During scan, only keeps current top 10
-- Time: O(N * log K) where K=10
-- Memory: only stores K rows
-- Much better than full sort O(N * log N)

6. COUNT(*) Optimization

6.1 Why InnoDB's COUNT(*) Is Slow

Unlike MyISAM, InnoDB does not cache exact row counts because of MVCC (Multi-Version Concurrency Control):

• Different transactions may see different row counts (depending on isolation level and row versions)
• Rows marked for deletion but not yet purged are visible to some transactions but not others
• Therefore, maintaining a global exact row counter is impossible

Executing SELECT COUNT(*) FROM big_table, InnoDB must scan a complete index (choosing the smallest secondary index) to count rows. For 100M rows, this may take 30-60 seconds.

6.2 COUNT(*) vs COUNT(1) vs COUNT(col)

6.3 Optimization Approaches

Approach 1: Approximate Count

-- From information_schema (error margin up to 40-50%)
SELECT TABLE_ROWS FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'mydb' AND TABLE_NAME = 'orders';

-- Using EXPLAIN (faster, error typically 10-30%)
EXPLAIN SELECT COUNT(*) FROM orders;
-- The "rows" column is the optimizer's estimate

Approach 2: Cached Count

-- Method A: Redis counter (INCR on INSERT, DECR on DELETE)
-- Method B: Database counter table with triggers
CREATE TABLE table_counts (
    table_name VARCHAR(64) PRIMARY KEY,
    row_count BIGINT UNSIGNED NOT NULL DEFAULT 0
);

-- Maintain with triggers
-- Query: O(1)
SELECT row_count FROM table_counts WHERE table_name = 'orders';

Approach 3: Summary Tables

-- Pre-aggregated daily stats
CREATE TABLE daily_order_stats (
    stat_date DATE PRIMARY KEY,
    total_count INT UNSIGNED NOT NULL DEFAULT 0,
    pending_count INT UNSIGNED NOT NULL DEFAULT 0
);
-- Refreshed periodically via cron job

Approach 4: Covering Index for Filtered COUNT

SELECT COUNT(*) FROM orders WHERE status = 'pending' AND created_at > '2024-01-01';
-- With covering index idx(status, created_at), only scans index
ALTER TABLE orders ADD INDEX idx_status_date (status, created_at);

7. Large UPDATE/DELETE Optimization

7.1 Why Bulk DML Is Problematic

A simple DELETE FROM orders WHERE created_at < '2020-01-01' affecting 5M rows causes:

• Long transaction: Undo log explosion, catastrophic rollback cost
• Lock contention: InnoDB row locks (actually next-key locks) held too long, blocking other DML
• Replication lag: A 30-minute transaction on the primary takes 30 minutes to replay on replicas
• Buffer Pool pollution: Many modified pages may evict hot data

7.2 Batch Processing

-- Batch delete (recommended approach)
-- Application-layer loop:
-- while True:
--     cursor.execute("""
--         DELETE FROM orders WHERE created_at < '2020-01-01'
--         ORDER BY id LIMIT 5000
--     """)
--     if cursor.rowcount == 0: break
--     connection.commit()
--     time.sleep(0.5)  # Let replicas catch up

Key points:

• ORDER BY id LIMIT N ensures deterministic row sets, avoids rescanning
• COMMIT after each batch releases locks and undo log
• Batch size 1,000-10,000 rows — too small = network overhead, too large = long transactions
• Sleep between batches gives replicas time to catch up

7.3 Partition Exchange for Bulk Delete

-- If table is partitioned by date
ALTER TABLE orders DROP PARTITION p202301;  -- Instant!

-- Or use EXCHANGE to preserve partition definition:
CREATE TABLE orders_archive LIKE orders;
ALTER TABLE orders_archive REMOVE PARTITIONING;
ALTER TABLE orders EXCHANGE PARTITION p202301 WITH TABLE orders_archive;
DROP TABLE orders_archive;  -- Or archive to cold storage

7.4 pt-archiver for Incremental Archival

# Percona Toolkit's pt-archiver
pt-archiver \
    --source h=localhost,D=mydb,t=orders \
    --where "created_at < '2020-01-01'" \
    --limit 5000 --commit-each \
    --check-slave-lag h=replica-host --max-lag 5 \
    --purge

8. Query Rewriting Patterns

Here are 15+ common query rewriting patterns with before/after comparisons:

-- BEFORE: OR across different columns may cause full scan
SELECT * FROM users WHERE email = '[email protected]' OR phone = '13800138000';

-- AFTER: Split into two queries, each using its own index
SELECT * FROM users WHERE email = '[email protected]'
UNION ALL
SELECT * FROM users WHERE phone = '13800138000' AND email != '[email protected]';

-- BEFORE: phone is VARCHAR but numeric value passed
SELECT * FROM users WHERE phone = 13800138000;  -- Index unusable!

-- AFTER: Use matching type
SELECT * FROM users WHERE phone = '13800138000';  -- Uses index

-- BEFORE: Function on indexed column
SELECT * FROM orders WHERE DATE(created_at) = '2024-03-15';

-- AFTER: Range condition
SELECT * FROM orders
WHERE created_at >= '2024-03-15' AND created_at < '2024-03-16';

-- Or: functional index (MySQL 8.0.13+)
ALTER TABLE orders ADD INDEX idx_date ((DATE(created_at)));

-- BEFORE: Leading wildcard, index unusable
SELECT * FROM articles WHERE title LIKE '%optimization%';

-- AFTER: Full-text index
ALTER TABLE articles ADD FULLTEXT INDEX ft_title (title);
SELECT * FROM articles WHERE MATCH(title) AGAINST('optimization' IN BOOLEAN MODE);

-- BEFORE: NULL trap + possible poor performance
SELECT * FROM users WHERE id NOT IN (SELECT user_id FROM blacklist);

-- AFTER: NULL-safe and optimized
SELECT * FROM users u
WHERE NOT EXISTS (SELECT 1 FROM blacklist b WHERE b.user_id = u.id);

-- BEFORE: Returns unnecessary columns, can't use covering index
SELECT * FROM orders WHERE user_id = 42;

-- AFTER: May hit covering index
SELECT id, amount, status FROM orders WHERE user_id = 42;
-- With idx(user_id, amount, status), uses covering index

-- BEFORE: UNION auto-deduplicates (requires sort or hash)
SELECT user_id FROM orders WHERE created_at > '2024-01-01'
UNION
SELECT user_id FROM returns WHERE created_at > '2024-01-01';

-- AFTER: Skip dedup if not needed
SELECT user_id FROM orders WHERE created_at > '2024-01-01'
UNION ALL
SELECT user_id FROM returns WHERE created_at > '2024-01-01';

-- BEFORE: Different charsets cause implicit conversion, index unusable
-- users.name is utf8mb4, old_logs.user_name is utf8
SELECT * FROM users u JOIN old_logs l ON u.name = l.user_name;

-- AFTER: Unify charsets
ALTER TABLE old_logs MODIFY user_name VARCHAR(100) CHARACTER SET utf8mb4;

-- BEFORE: 3 separate queries
-- q1: SELECT AVG(salary) FROM emp WHERE dept='Eng';
-- q2: SELECT AVG(salary) FROM emp WHERE dept='Sales';
-- q3: SELECT AVG(salary) FROM emp WHERE dept='HR';

-- AFTER: Single query
SELECT
    AVG(CASE WHEN dept='Eng' THEN salary END) AS eng_avg,
    AVG(CASE WHEN dept='Sales' THEN salary END) AS sales_avg,
    AVG(CASE WHEN dept='HR' THEN salary END) AS hr_avg
FROM emp;

-- REPLACE = DELETE + INSERT (triggers delete trigger, reassigns AUTO_INCREMENT)
-- INSERT ... ON DUPLICATE KEY UPDATE = true UPDATE (preserves original row ID)

-- Prefer:
INSERT INTO daily_stats (stat_date, cnt) VALUES ('2024-03-15', 100)
ON DUPLICATE KEY UPDATE cnt = VALUES(cnt);
-- Not: REPLACE INTO daily_stats ...

-- BEFORE
SELECT * FROM articles ORDER BY id DESC LIMIT 100000, 20;

-- AFTER
SELECT * FROM articles WHERE id < last_seen_id ORDER BY id DESC LIMIT 20;

-- BEFORE: Cross-table delete, heavy locking
DELETE o, oi FROM orders o JOIN order_items oi ON o.id = oi.order_id
WHERE o.created_at < '2020-01-01';

-- AFTER: Child table first, then parent, in batches
DELETE FROM order_items WHERE order_id IN (
    SELECT id FROM orders WHERE created_at < '2020-01-01'
) LIMIT 5000;
DELETE FROM orders WHERE created_at < '2020-01-01' LIMIT 5000;

-- BEFORE: Arithmetic on indexed column, index unusable
SELECT * FROM orders WHERE id + 1 = 10;

-- AFTER
SELECT * FROM orders WHERE id = 9;

-- BEFORE: HAVING filters after grouping
SELECT dept_id, COUNT(*) AS cnt FROM employees
GROUP BY dept_id HAVING dept_id > 5;

-- AFTER: WHERE filters before grouping (fewer rows to aggregate)
SELECT dept_id, COUNT(*) AS cnt FROM employees
WHERE dept_id > 5 GROUP BY dept_id;

-- BEFORE: N+1 problem
-- users = query("SELECT * FROM users LIMIT 100")
-- for user in users:
--     orders = query("SELECT * FROM orders WHERE user_id = ?", user.id)

-- AFTER: Single JOIN
SELECT u.*, o.* FROM users u
LEFT JOIN orders o ON u.id = o.user_id
LIMIT 100;

-- Or batch IN:
SELECT * FROM orders WHERE user_id IN (1, 2, 3, ..., 100);

9. Query Profiling

9.1 SHOW PROFILE (Deprecated but Useful)

SET profiling = 1;

SELECT * FROM orders WHERE user_id = 42 ORDER BY created_at DESC LIMIT 10;

SHOW PROFILES;
SHOW PROFILE FOR QUERY 1;
+--------------------------------+-----------+
| Status                         | Duration  |
+--------------------------------+-----------+
| starting                       | 0.000012  |
| checking permissions           | 0.000005  |
| Opening tables                 | 0.000018  |
| optimizing                     | 0.000015  |
| statistics                     | 0.000042  |
| executing                      | 0.001850  | ← Main cost in execution
| cleaning up                    | 0.000003  |
+--------------------------------+-----------+

SHOW PROFILE CPU, BLOCK IO FOR QUERY 1;

9.2 Performance Schema

-- Top 10 slowest queries by total execution time
SELECT
    DIGEST_TEXT,
    COUNT_STAR AS exec_count,
    ROUND(SUM_TIMER_WAIT / 1e12, 3) AS total_sec,
    ROUND(AVG_TIMER_WAIT / 1e12, 3) AS avg_sec,
    SUM_ROWS_EXAMINED AS rows_examined,
    SUM_ROWS_SENT AS rows_sent,
    ROUND(SUM_ROWS_EXAMINED / NULLIF(SUM_ROWS_SENT, 0), 1) AS examine_per_sent
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC LIMIT 10;

9.3 sys Schema

-- Most resource-intensive statements
SELECT * FROM sys.statement_analysis ORDER BY avg_latency DESC LIMIT 10;

-- Statements with full table scans
SELECT * FROM sys.statements_with_full_table_scans LIMIT 10;

-- Unused indexes (candidates for removal)
SELECT * FROM sys.schema_unused_indexes;

-- Redundant indexes
SELECT * FROM sys.schema_redundant_indexes;

9.4 Optimizer Trace

SET optimizer_trace = 'enabled=on';

SELECT * FROM orders o JOIN users u ON o.user_id = u.id
WHERE o.status = 'pending' ORDER BY o.created_at DESC LIMIT 10;

SELECT * FROM information_schema.OPTIMIZER_TRACE\G

-- Key JSON sections:
-- "join_optimization" → "rows_estimation": row count estimates per table/index
-- "considered_execution_plans": evaluated JOIN orders and their costs
-- "chosen_plan": the selected plan
-- "reconsidering_access_paths_for_index_ordering": index choice changes due to ORDER BY

SET optimizer_trace = 'enabled=off';

9.5 EXPLAIN ANALYZE Workflow

EXPLAIN ANALYZE
SELECT o.*, u.name FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.status = 'pending' ORDER BY o.created_at DESC LIMIT 10\G

-- Output example:
-- -> Limit: 10 row(s)  (cost=45.2 rows=10) (actual time=0.85..0.92 rows=10 loops=1)
--     -> Sort: o.created_at DESC, limit input to 10 row(s)
--         -> Nested loop join  (cost=45.2 rows=100)
--            (actual time=0.12..0.78 rows=100 loops=1)
--             -> Index lookup on o using idx_status (status='pending')
--                (cost=22.1 rows=100) (actual time=0.08..0.35 rows=100 loops=1)
--             -> Single-row index lookup on u using PRIMARY (id=o.user_id)
--                (cost=0.25 rows=1) (actual time=0.003..0.003 rows=1 loops=100)

-- Key comparisons:
-- cost vs actual time: estimate vs reality
-- rows (estimated) vs rows (actual): large gap = stale statistics
-- loops: how many times the operation executed

10. Query Hints

10.1 Traditional Index Hints

-- USE INDEX: Suggest index (optimizer may still ignore)
SELECT * FROM orders USE INDEX (idx_user_date)
WHERE user_id = 42 AND created_at > '2024-01-01';

-- FORCE INDEX: Force index usage (stronger than USE INDEX)
SELECT * FROM orders FORCE INDEX (idx_user_date)
WHERE user_id = 42 AND created_at > '2024-01-01';

-- IGNORE INDEX: Exclude specific index
SELECT * FROM orders IGNORE INDEX (idx_status)
WHERE status = 'pending' AND user_id = 42;

10.2 MySQL 8.0 Optimizer Hints

-- JOIN order control
SELECT /*+ JOIN_ORDER(u, o) */ * FROM orders o JOIN users u ON o.user_id = u.id;

-- JOIN algorithm control
SELECT /*+ HASH_JOIN(o) */ * FROM orders o JOIN users u ON o.user_id = u.id;

-- Disable ICP
SELECT /*+ NO_ICP(orders) */ * FROM orders WHERE user_id = 42 AND amount > 100;

-- Derived table merge control
SELECT /*+ MERGE(derived_t) */ * FROM (SELECT ...) AS derived_t;
SELECT /*+ NO_MERGE(derived_t) */ * FROM (SELECT ...) AS derived_t;

-- Semi-join strategy
SELECT /*+ SEMIJOIN(@subq1 FIRSTMATCH) */ *
FROM orders WHERE user_id IN (SELECT /*+ QB_NAME(subq1) */ id FROM users);

-- SET_VAR: Temporarily modify system variable during query
SELECT /*+ SET_VAR(sort_buffer_size = 8388608) */ *
FROM articles ORDER BY score DESC LIMIT 100;

10.3 When to Use Hints

• Appropriate: Stale statistics causing wrong index choice; known data distribution the optimizer can't infer; special pagination queries
• Code smell: Heavy hint usage indicates deeper issues — poor index design, stale statistics, or query structure needing refactoring
• Maintenance burden: Hints move plan decisions from optimizer to application code. Schema or data changes may make hints harmful
• Recommendation: First try ANALYZE TABLE, then index optimization, then hints as temporary workaround

10.4 optimizer_switch Flags

SELECT @@optimizer_switch\G

-- Key switches:
-- index_condition_pushdown=on     -- ICP
-- mrr=on                          -- Multi-Range Read
-- batched_key_access=off          -- BKA (off by default)
-- block_nested_loop=on            -- BNL / Hash Join
-- semijoin=on                     -- Semi-join transforms
-- firstmatch=on                   -- FirstMatch strategy
-- loosescan=on                    -- LooseScan strategy
-- materialization=on              -- Subquery materialization
-- hash_join=on                    -- Hash Join (8.0.18+)
-- skip_scan=on                    -- Skip Scan (8.0.13+)
-- subquery_to_derived=off         -- Subquery → derived (8.0.21+)
-- prefer_ordering_index=on        -- Prefer sorting index (8.0.21+)
-- hypergraph_optimizer=off        -- Hypergraph optimizer (8.0.31+, experimental)

-- Modify single switch
SET optimizer_switch = 'batched_key_access=on';

11. Real-World Case Studies

-- Original query (3.5s response time)
SELECT * FROM products
WHERE title LIKE '%bluetooth earbuds%'
  AND category_id = 42 AND price BETWEEN 10 AND 50 AND rating >= 4.0
ORDER BY sales_count DESC LIMIT 100000, 20;

-- Problems:
-- 1. LIKE '%...%' can't use B+Tree index
-- 2. Multiple filters make index selection difficult
-- 3. ORDER BY sales_count conflicts with filter indexes → filesort
-- 4. LIMIT 100000, 20 large offset

-- Optimization:
-- Step 1: Full-text index for keyword search
ALTER TABLE products ADD FULLTEXT INDEX ft_title (title) WITH PARSER ngram;

-- Step 2: Composite index for filters + sort
ALTER TABLE products ADD INDEX idx_cat_rating_sales (category_id, rating, sales_count);

-- Step 3: Deferred JOIN + cursor pagination
-- Result: 3.5s → 45ms (deferred JOIN) → 8ms (cursor pagination)

-- Original query (12s)
SELECT DATE(created_at) AS day, COUNT(*) AS cnt, SUM(amount) AS total,
    SUM(CASE WHEN status='refunded' THEN 1 ELSE 0 END) AS refunds
FROM orders
WHERE created_at >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
GROUP BY DATE(created_at) ORDER BY day;

-- Optimization A: Covering index + range rewrite → 12s → 1.2s
ALTER TABLE orders ADD INDEX idx_created_status_amount (created_at, status, amount);

-- Optimization B: Summary table → 1.2s → 0.5ms
CREATE TABLE daily_order_summary (
    summary_date DATE PRIMARY KEY,
    order_count INT UNSIGNED NOT NULL,
    total_amount DECIMAL(15,2) NOT NULL,
    refund_count INT UNSIGNED NOT NULL
);
-- Refresh hourly via cron job

-- Original query (8s)
SELECT p.* FROM posts p
JOIN follows f ON p.user_id = f.following_id
WHERE f.follower_id = 12345
ORDER BY p.created_at DESC LIMIT 20;

-- Problem: User follows 500 people → JOIN matches 500K posts → sort all → take 20

-- Solution: Fan-out on Write (Push Model)
CREATE TABLE feed_inbox (
    user_id BIGINT NOT NULL,
    post_id BIGINT NOT NULL,
    created_at DATETIME NOT NULL,
    PRIMARY KEY (user_id, created_at, post_id),
    INDEX idx_user_time (user_id, created_at DESC)
);

-- When a post is published, async write to all followers' inboxes
-- Query: 8s → 3ms (covering index + sequential scan)
SELECT fi.post_id, p.content FROM feed_inbox fi
JOIN posts p ON fi.post_id = p.id
WHERE fi.user_id = 12345
ORDER BY fi.created_at DESC LIMIT 20;

-- Trade-off: Write amplification (1 post → N inbox writes)
-- For high-follower accounts, use pull model or hybrid approach

12. Anti-Patterns Checklist

13. FAQ