MySQL / Concurrency, performance, and operations / Lesson 14
Optimizer evidence and sustainable performance
Improve a query family using representative cardinalities, bounded work, and write-aware index choices.
On this page
Lesson goals
- Explain selectivity, statistics, covering, and skew.
- Use keyset pagination and system-level evidence without universal tuning formulas.
Optimize a workload rather than a screenshot
Use the shop fixture for logical results only. A real workload includes arrival rate, parameter distribution, response targets, write volume, and concurrency. Optimizing one rare report at the expense of every purchase can make the system worse. Group slow statements by normalized shape, measure frequency and total time, then choose a high-impact family. Avoid collecting sensitive parameter values when aggregated diagnostics suffice.
Statistics estimate uneven reality
A customer with two orders and a customer with millions require different access work. Optimizer statistics estimate cardinalities, and skew can make an average estimate misleading. Histograms can help supported nonindexed-column selectivity estimates, but collecting statistics is an operational change requiring scope and cost review. Compare estimated and actual rows before assuming the optimizer is irrational or adding a FORCE INDEX hint.
Give indexes usable predicates
Equality predicates followed by a bounded range often fit a composite B-tree. Functions on an indexed column, incompatible comparisons, and leading-wildcard searches can prevent the desired lookup. Rewrite the business condition first; do not apply a rewrite that changes time zones or NULL semantics merely to obtain a range scan. A covering index can reduce row fetches, but its extra columns increase storage and write amplification.
EXPLAIN FORMAT=TREE
SELECT id FROM orders
WHERE placed_at>='2026-01-01' AND placed_at<'2026-02-01'
ORDER BY placed_at,id;
SELECT id FROM orders
WHERE placed_at>='2026-01-01' AND placed_at<'2026-02-01'
ORDER BY placed_at,id;id
101
102
103The query returns 101, 102, and 103. A time-leading index might support the range and ordering, whereas the optional customer-leading index does not automatically provide the same access. The exact plan remains data-dependent; the example asserts results, not a fabricated range-scan guarantee.
Bound the next page by its last key
Large OFFSET pagination repeatedly walks past already seen rows. Keyset pagination uses the last displayed ordering tuple to seek subsequent rows. A unique tie breaker is required. It improves continuity under some concurrent changes but does not create a frozen multi-request snapshot; deletion or updates to sort keys can still change membership. Decide whether the product needs a live feed or a stable exported report.
SELECT id,placed_at FROM orders
WHERE placed_at>'2026-01-10 10:00:00'
OR (placed_at='2026-01-10 10:00:00' AND id>101)
ORDER BY placed_at,id LIMIT 2;id | placed_at
102 | 2026-01-11 11:00:00
103 | 2026-01-12 12:00:00The last-seen tuple is the time and id of 101. The predicate returns 102 and 103 without requesting a numeric offset. For customer-specific pages, wrap this OR expression in parentheses before adding AND customer_id = ?, otherwise precedence can accidentally broaden the population.
Separate CPU, I/O, and waiting
High latency can come from row work, disk reads, lock waits, connection queues, or application round trips. More buffer-pool memory may help an I/O-bound working set but cannot repair a deadlock or an unbounded report. Use Performance Schema summaries and operating-system evidence in an authorized environment. Change one variable at a time, compare under similar load, and retain rollback criteria and write-latency measurements.
Common errors and fixes
An average-case plan performs badly for one huge customer.
- Why it happens
- Skew makes the representative cardinality assumption false.
- How to fix it
- Compare common and extreme parameter plans and actual row counts.
- Prevent it next time
- Include skew and growth in benchmark datasets.
More indexes increase purchase latency.
- Why it happens
- Every write maintains additional B-trees and consumes cache capacity.
- How to fix it
- Remove only proven redundant or unused candidates through reviewed migrations.
- Prevent it next time
- Evaluate write amplification alongside read gains.
Keyset filtering leaks another customer's orders.
- Why it happens
- An unparenthesized OR bypassed a shared customer predicate.
- How to fix it
- Group the full cursor condition before applying customer_id filtering.
- Prevent it next time
- Review Boolean precedence and tenant scope together.
Check your understanding
1. What measurements justify replacing OFFSET with keyset pagination?
Show worked solution
Compare examined rows and latency for early and deep pages at representative scale. Verify stable unique ordering and required navigation behavior. Keyset is attractive when work grows with page depth and sequential next-page access fits the product; arbitrary numbered jumps may require a different design.
2. A query is slow but examines few rows. Why might another index fail to help?
Show worked solution
It may be waiting on locks, disk, connection capacity, or the application network. Correlate elapsed time with wait events and resource saturation before changing access paths. A new index could add write cost while leaving the true wait unchanged.
Key takeaways
- Optimize total workload cost using representative evidence.
- Bound work, preserve semantics, and measure write consequences.