Query Optimization

MySQL's cost-based optimizer chooses access methods (index vs scan), join order, and join algorithms using index statistics and histograms.

Concept

MySQL's cost-based optimizer chooses access methods (index vs scan), join order, and join algorithms using index statistics and histograms. Tuning means measuring (slow query log, Performance Schema), explaining (EXPLAIN, EXPLAIN ANALYZE), and fixing (indexes, rewrites, statistics, schema changes).

Why It Matters

A handful of slow queries usually account for most database load. Fixing them improves response times and capacity far more cheaply than hardware.

Syntax

EXPLAIN SELECT ...;                          -- traditional table output
EXPLAIN FORMAT=TREE SELECT ...;              -- plan tree (8.0.16+)
EXPLAIN FORMAT=JSON SELECT ...;              -- detailed costs
EXPLAIN ANALYZE SELECT ...;                  -- EXECUTES the query and shows actual rows and timing (8.0.18+)
EXPLAIN FOR CONNECTION <id>;                 -- plan of a running statement
SELECT /*+ INDEX(t ix_name) JOIN_ORDER(a, b) */ ...;   -- optimizer hints

Example

32.1 Reading EXPLAIN Illustrative output (small tables):

type column (best → worst):

system / const At most one row via PK/unique equality One row per row of the previous table via PK/unique (ideal join)

Several rows via non-unique index equality fulltext, ref_or_null, index_merge Special access methods

Index range scan (BETWEEN, <, IN, LIKE 'x%') Full scan of an index Full table scan Important Extra values:

Covering index (no table row lookups) — good Rows filtered after reading Using index condition Index Condition Pushdown Using temporary

Internal temporary table (GROUP BY/DISTINCT/UNION) Using filesort

Extra sort step (ORDER BY not satisfied by an index) Using join buffer (hash join)

Hash join (no usable index on the join column) 32.2 EXPLAIN ANALYZE

Compare rows (estimate) with actual ... rows; large differences suggest stale statistics or a missing histogram. EXPLAIN ANALYZE runs the query — wrap DML in a transaction or avoid it on production writes.

32.3 Finding slow queries Summarize the slow log with mysqldumpslow (bundled) or pt-query-digest (Percona Toolkit).

A key ratio: rows examined vs rows sent. Examining 1,000,000 rows to return 10 means a missing or unusable index.

32.4 Common optimizations 32.5 Statistics, histograms, and hints

Use hints sparingly; they freeze decisions that may become wrong as data changes. Fix statistics and indexes first.

32.6 Troubleshooting process

EXPLAIN
SELECT c.name, SUM(o.total_amount)
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
WHERE o.order_date >= '2026-02-01'
GROUP BY c.name;

+----+-------------+-------+--------+---------------------+---------+---------+---------------------------+------+----------+------------------------------+
| id | select_type | table | type   | possible_keys       | key     | key_len | ref                       | rows | filtered | Extra                        |
+----+-------------+-------+--------+---------------------+---------+---------+---------------------------+------+----------+------------------------------+
|  1 | SIMPLE      | o     | ALL    | ix_orders_customer  | NULL    | NULL    | NULL                      |    6 |    33.33 | Using where; Using temporary |
|  1 | SIMPLE      | c     | eq_ref | PRIMARY             | PRIMARY | 4       | eh_company.o.customer_id  |    1 |   100.00 | NULL                         |
+----+-------------+-------+--------+---------------------+---------+---------+---------------------------+------+----------+------------------------------+

EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 2 ORDER BY order_date;

-> Sort: orders.order_date  (cost=1.10 rows=2) (actual time=0.052..0.053 rows=2 loops=1)
    -> Index lookup on orders using ix_orders_customer (customer_id=2)  (cost=0.70 rows=2) (actual time=0.031..0.036 rows=2 loops=1)

-- Slow query log
SET PERSIST slow_query_log = ON;
SET PERSIST long_query_time = 0.5;                   -- seconds
SET PERSIST log_queries_not_using_indexes = OFF;     -- can be noisy

-- Top statements by total latency (sys schema, from Performance Schema digests)
SELECT query, exec_count, total_latency, avg_latency, rows_examined_avg, rows_sent_avg
FROM sys.statement_analysis
ORDER BY total_latency DESC
LIMIT 10;

SELECT * FROM sys.statements_with_full_table_scans LIMIT 10;
SELECT * FROM sys.statements_with_temp_tables LIMIT 10;
SELECT * FROM sys.statements_with_sorting LIMIT 10;

-- 1. Sargable ranges instead of functions on columns
SELECT * FROM orders WHERE YEAR(order_date) = 2026;                                    -- slow
SELECT * FROM orders WHERE order_date >= '2026-01-01' AND order_date < '2027-01-01';   -- index range

-- 2. Composite index matching filter + sort, and LIMIT
CREATE INDEX ix_dtc_vehicle_status_time ON dtc_events (vehicle_id, status, detected_at);
SELECT * FROM dtc_events WHERE vehicle_id = 7 AND status = 'ACTIVE' ORDER BY detected_at DESC LIMIT 20;

-- 3. Covering index for hot queries
CREATE INDEX ix_orders_cust_date_amt ON orders (customer_id, order_date, total_amount);
SELECT order_date, total_amount FROM orders WHERE customer_id = 2;      -- Extra: Using index

-- 4. Keyset pagination instead of large OFFSET
SELECT * FROM can_log WHERE log_id > 123456789 ORDER BY log_id LIMIT 1000;

-- 5. Avoid SELECT * on wide tables (TEXT/JSON columns cost I/O)

-- 6. EXISTS instead of COUNT(*) for existence
SELECT EXISTS (SELECT 1 FROM orders WHERE customer_id = 5) AS has_orders;

-- 7. Match types and collations in joins and comparisons (no implicit conversions)

-- 8. Batch writes: multi-row INSERT, LOAD DATA, chunked UPDATE/DELETE with LIMIT

ANALYZE TABLE orders;                                             -- refresh index stats
ANALYZE TABLE dtc_events UPDATE HISTOGRAM ON status, dtc_code WITH 32 BUCKETS;

SELECT /*+ INDEX(o ix_orders_customer) */ * FROM orders o WHERE customer_id = 2;
SELECT /*+ NO_INDEX(o ix_orders_status_date) */ ...;
SELECT /*+ JOIN_ORDER(c, o) HASH_JOIN(o) */ ...;
SELECT /*+ MAX_EXECUTION_TIME(2000) */ * FROM big_report_view;   -- abort SELECT after 2 s
SELECT /*+ SET_VAR(sort_buffer_size = 16M) */ ... ORDER BY ...;

1. Find: slow log / sys.statement_analysis → highest total latency and rows_examined
2. Reproduce with real parameters
3. EXPLAIN and EXPLAIN ANALYZE → access types, rows, filesort/temporary, join algorithm
4. Compare estimated vs actual rows → ANALYZE TABLE / histograms
5. Check indexes (SHOW INDEX), sargability, type/collation mismatches
6. Check locking (sys.innodb_lock_waits) — slow may mean "waiting", not "working"
7. Apply one fix; re-measure on production-like data volume
8. Monitor for regressions

Expected Result

After adding ix_orders_cust_date_amt:

EXPLAIN SELECT order_date, total_amount FROM orders WHERE customer_id = 2\G

type: ref
          key: ix_orders_cust_date_amt
         rows: 2
        Extra: Using index

Common Mistakes

Optimizing without measuring.

Believing type: ALL is always bad (small tables, large result fractions).

Ignoring Using filesort on large results with LIMIT.

Running EXPLAIN ANALYZE on production DELETEs.

Adding hints instead of fixing indexes/statistics.

Best Practices

Enable the slow log (with a sensible threshold) and review sys.statement_analysis regularly.

Design indexes for the top queries; verify with EXPLAIN.

Keep statistics fresh; use histograms for skewed non-indexed columns.

Test with realistic data volumes.

Interview Relevance

Very high: "How do you optimize a slow MySQL query?", "Explain EXPLAIN output (type, key, rows, Extra)", "What does Using filesort / Using temporary mean?", "EXPLAIN vs EXPLAIN ANALYZE?", "What is a covering index?".

Practice Questions

Generate 1 million rows in can_log with a recursive CTE and compare EXPLAIN before/after adding an index.

Find the top 5 statements by total latency on your server.

Rewrite a query using DATE(captured_at) = '2026-03-10' to be sargable.

Use a histogram to improve an estimate for a skewed status column.