SQL Query Optimization

When to use

• The user needs to rewrite a query — remove a function on a column, replace OFFSET paging, avoid SELECT *, or add caching — so it runs efficiently.
• The user has a slow request or report where profiling points at the database, and wants to find and speed up the offending query.
• The user wants to enable and read the MySQL slow query log (or performance_schema) to find the queries costing the most total time.

When NOT to use

• Do not optimize queries when profiling shows the time is in PHP CPU, serialization, or an external API, not in database wait.
• Do not hand-tune queries that are already fast and infrequent just because they could theoretically be faster.

Core guidance

• EXPLAIN the query, then add the index it needs — Run EXPLAIN on the slow query; if type is ALL or rows is large, add a composite index covering the WHERE, JOIN, and ORDER BY columns in leftmost-prefix order, then re-EXPLAIN to confirm the plan improved.
• Measure before optimizing, then target the worst query — Start from data: enable the slow query log or read performance_schema, rank queries by total time (frequency x duration), and optimize the top offender first — never guess which query is slow.

Quick recipe

1. Enable the slow query log (or read performance_schema), aggregate with a tool like pt-query-digest, and pick the query with the highest total time. (verify: You have one specific slow statement ranked by frequency x duration.)
2. EXPLAIN the query against production-scale data; add a composite/covering index for a scan, make predicates sargable, trim SELECT *, and replace deep OFFSET paging with keyset paging. (verify: EXPLAIN type improves to ref/range, rows drops sharply, and no filesort remains.)
3. Re-run the workload and confirm latency and rows-examined dropped. If the now-lean query is still hot, add a TTL cache with jitter and single-flight protection; otherwise stop. (verify: Measured p95 latency for the query fell and, if cached, a cache miss no longer stampedes the database.)

Best practices

• Collapse a loop of single-row INSERT/UPDATE/SELECT into one set-based statement (multi-row INSERT, WHERE id IN (...), or a JOIN update) to cut network round-trips
• Once a query is indexed and rewritten, cache the result (Redis/APCu) only for reads that are hot and tolerate slight staleness, always with a TTL and an invalidation-on-write path
• Replace LIMIT ? OFFSET N on large tables with keyset (seek) pagination — WHERE id < ? ORDER BY id DESC LIMIT ? — so deep pages do not scan and discard N rows
• Replace SELECT * with the explicit columns the code reads, so the query can be covered by an index and transfers less data over the wire

Anti-patterns

See references/anti-patterns.md.

Common mistakes

1. Caching masks a slow query until the cache misses — Wrapping a slow, unindexed query in a cache makes warm requests fast but leaves every cold start, deploy, or invalidation exposed to the full slow query — often a thundering herd. Fix: Fix the query (index, rewrite) first, then cache; on top, add TTL jitter and a single-flight lock so a miss cannot stampede the database.
2. EXPLAIN on an empty or tiny table misleads you — Running EXPLAIN against a dev database with a handful of rows shows a fast plan even when production with millions of rows will pick a different, slow plan. Fix: Test EXPLAIN against production-scale data (a copy or realistic seed) and run ANALYZE TABLE so statistics reflect real cardinality.
3. OFFSET pagination gets slower the deeper you page — LIMIT 20 OFFSET 100000 must read and discard 100,000 rows before returning 20, so page latency grows linearly with page number even with an index. Fix: Switch to keyset/seek pagination on an indexed, ordered column (WHERE id < :lastId ORDER BY id DESC LIMIT 20) so each page seeks directly.
4. OR across different columns can defeat the index — A predicate like WHERE email = ? OR phone = ? often cannot use a single index and falls back to a full scan, even when both columns are individually indexed. Fix: Rewrite as a UNION of two indexed single-column queries, or add an index the optimizer can use via index merge, and confirm with EXPLAIN.

Version notes

• EXPLAIN ANALYZE and better optimizer tools in MySQL 8 (PHP >=7.4) — MySQL 8.0 adds EXPLAIN ANALYZE (real measured execution), a cost-based optimizer trace, and hash joins; MySQL 5.7 offers only estimate-based EXPLAIN and no hash join. performance_schema slow-query data is available on both.

Security

• Optimize with bound parameters and allow-listed sort columns — When rewriting queries for speed, keep every user value bound as a parameter and validate any dynamic column, direction, or table name against a fixed allow-list — never interpolate them to "help" the optimizer.

Testing

• Activation and decision behavior are verified in eval/scenarios/performance/query-optimization.yaml.

Additional resources

• references/anti-patterns.md
• references/core.md
• references/examples.md
• references/pitfalls.md
• references/recipes.md
• references/version-differences.md

Knowledge graph

Related: databases/mysql-indexing, performance/n-plus-one, performance/opcache