Query Optimization in MySQL

Query Optimization Starts with Measurement

Query optimization is the disciplined process of reducing total database work while preserving correct results, concurrency and maintainability. It includes SQL shape, schema, indexes, statistics, application call patterns, transactions, memory and storage—not just “add an index.”

1. Find high-impact queries by latency, frequency, rows examined, lock time or resource use.
2. Capture the exact SQL, bound values and result requirements.
3. Reproduce representative volume, distribution and concurrency.
4. Read EXPLAIN and safely measure EXPLAIN ANALYZE.
5. Change one justified factor and compare the same metrics.

The slow query log can identify statements exceeding configured thresholds. Combine it with application traces and business context because average latency alone can hide rare bad parameter values.

Verified Orders Optimization Lab

DROP TABLE IF EXISTS orders_opt_lab;
DROP TABLE IF EXISTS customers_opt_lab;

CREATE TABLE customers_opt_lab (
  customer_id INT PRIMARY KEY,
  customer_name VARCHAR(80) NOT NULL
) ENGINE = InnoDB;

CREATE TABLE orders_opt_lab (
  order_id INT PRIMARY KEY,
  customer_id INT NOT NULL,
  status VARCHAR(20) NOT NULL,
  order_date DATE NOT NULL,
  total_amount DECIMAL(10,2) NOT NULL,
  notes VARCHAR(100),
  CONSTRAINT fk_opt_customer
    FOREIGN KEY (customer_id) REFERENCES customers_opt_lab(customer_id)
) ENGINE = InnoDB;

INSERT INTO customers_opt_lab VALUES
(101,'Asha'),(102,'Bilal'),(103,'Charu'),(104,'Deepak');

INSERT INTO orders_opt_lab VALUES
(1,101,'PAID',     '2026-08-01',1200.00,'School books'),
(2,101,'PENDING',  '2026-08-05', 500.00,'Stationery'),
(3,102,'PAID',     '2026-08-03', 750.00,'Uniform'),
(4,103,'CANCELLED','2026-08-04', 300.00,'Art kit'),
(5,101,'PAID',     '2026-08-10',1500.00,'Lab equipment'),
(6,102,'PENDING',  '2026-08-11', 900.00,'Sports kit'),
(7,104,'PAID',     '2026-08-12',2200.00,'Computer accessory'),
(8,101,'PAID',     '2026-08-14', 650.00,'Notebooks');

SELECT COUNT(*) AS paid_orders,
       SUM(total_amount) AS paid_total
FROM orders_opt_lab
WHERE customer_id = 101 AND status = 'PAID';

The lab verifies rewrites against a known result. It cannot prove speed with eight rows. Multiply data with realistic distributions in a separate test database before benchmarking.

Write Sargable, Type-Correct Predicates

A predicate is commonly called sargable when a suitable index can use it to define a search range. Compare these logically equivalent date filters:

SELECT order_id, order_date, total_amount
FROM orders_opt_lab
WHERE order_date >= '2026-08-01'
  AND order_date <  '2026-09-01'
ORDER BY order_date, order_id;

Keep join-key data types and collations compatible. Avoid implicit conversions that can block efficient lookup or change comparison rules. If expression search is a real requirement, consider an appropriate functional key part or indexed generated column and verify its plan.

Design Indexes for Query Shapes, Not Columns

CREATE INDEX idx_customer_status_date_amount
ON orders_opt_lab
  (customer_id, status, order_date, total_amount);

This index aligns with customer and status equality, a date range/order, and can cover total_amount for suitable queries. It supports leftmost prefixes beginning with customer_id; it does not provide the same direct lookup for status-only reports.

EXPLAIN FORMAT=TREE
SELECT order_id, order_date, total_amount
FROM orders_opt_lab
WHERE customer_id = 101
  AND status = 'PAID'
  AND order_date >= '2026-08-01'
  AND order_date <  '2026-09-01'
ORDER BY order_date;

• Place equality and join patterns deliberately before the relevant range/order parts.
• Apply the leftmost-prefix rule to every important query.
• Use covering columns only when measured saved lookups justify added width.
• Remove redundant indexes cautiously; invisible-index testing offers a reversible trial.
• Count write, buffer-pool and storage cost for every index.
• Keep InnoDB primary keys short and stable because secondary records include them.

Do not index every WHERE column independently. A few workload-aligned composite indexes often serve better than many overlapping indexes.

Optimize Joins, Aggregation and Application Calls

SELECT c.customer_id, c.customer_name,
       COUNT(*) AS paid_orders,
       SUM(o.total_amount) AS paid_total
FROM customers_opt_lab AS c
JOIN orders_opt_lab AS o
  ON o.customer_id = c.customer_id
WHERE o.status = 'PAID'
GROUP BY c.customer_id, c.customer_name
ORDER BY paid_total DESC, c.customer_id;

Verifying output is part of optimization because a faster wrong query is a defect.

• Index foreign/join keys according to join direction and filtering needs.
• Aggregate after correct filtering; pre-aggregate a large many-side when it reduces join fan-out.
• Avoid accidental Cartesian products and mismatched key types.
• Replace N+1 application queries with a set query, batch fetch or justified cache.
• Select only required columns rather than SELECT *.
• Use EXISTS when asking whether a related row exists; do not fetch unused detail.

MySQL chooses join order by cost. Written table order is not normally execution order, so read the plan rather than assuming “filter early” from textual placement.

Optimize ORDER BY, LIMIT and Pagination

A matching index can avoid a separate sort; otherwise Using filesort may be entirely reasonable. Always use deterministic ordering when pages must be stable.

-- Simple and fine for shallow pages
SELECT order_id, order_date, total_amount
FROM orders_opt_lab
ORDER BY order_date DESC, order_id DESC
LIMIT 20 OFFSET 40;

Deep OFFSET requires finding and discarding earlier rows. For sequential next-page navigation, keyset pagination resumes after the last seen key:

CREATE INDEX idx_date_id
ON orders_opt_lab (order_date DESC, order_id DESC);

SELECT order_id, order_date, total_amount
FROM orders_opt_lab
WHERE (order_date, order_id) < ('2026-08-10', 5)
ORDER BY order_date DESC, order_id DESC
LIMIT 20;

Keyset pagination is fast and stable for next/previous navigation but does not naturally jump to arbitrary page 500. Choose based on product requirements. When ORDER BY values can tie, include a unique tie-breaker.

Statistics, Plan Estimates and Safe Controls

ANALYZE TABLE orders_opt_lab;

SHOW INDEX FROM orders_opt_lab;

EXPLAIN FORMAT=JSON
SELECT order_id, total_amount
FROM orders_opt_lab
WHERE status = 'PAID';

MySQL's cost-based optimizer uses table and index statistics to estimate selectivity and cost. Major data changes, skew or correlated columns can make estimates differ from reality. ANALYZE TABLE refreshes key distribution; optional histograms can improve selectivity estimates for appropriate columns, especially nonindexed ones.

ANALYZE TABLE orders_opt_lab
UPDATE HISTOGRAM ON status WITH 16 BUCKETS;

ANALYZE TABLE orders_opt_lab
DROP HISTOGRAM ON status;

Histograms help estimation; they do not provide row access like an index. Compare EXPLAIN estimates with EXPLAIN ANALYZE actual rows. Use index or optimizer hints only after fixing data types, query shape, index design and statistics. Hints can become stale as data evolves.

Production Optimization Checklist

1. Define the service goal: latency percentile, throughput, freshness and acceptable load.
2. Capture top SQL by total impact, not only longest single execution.
3. Preserve exact parameters and verify the expected result.
4. Reproduce representative rows, skew, cache state and concurrency.
5. Inspect TREE/JSON EXPLAIN and safely run EXPLAIN ANALYZE.
6. Fix logical excess: N+1 calls, unused columns, duplicate work and late filtering.
7. Rewrite non-sargable predicates without changing semantics.
8. Design minimal composite indexes; check redundancy and write cost.
9. Refresh statistics when justified and compare estimates with actuals.
10. Benchmark before/after repeatedly; include cold/warm behavior and tail latency.
11. Deploy gradually, monitor plan/runtime regressions and keep rollback SQL.
12. Revisit after growth because optimal plans and indexes are workload-dependent.

Do not optimize by myth: “indexes always win,” “joins are slow,” “subqueries are slow,” “CTEs are faster,” and “filesort means disk” are not reliable rules. Measure the concrete plan.

Related lessons: MySQL indexes, composite indexes, EXPLAIN and locks and deadlocks.

Official References

• MySQL 8.4: Optimization
• MySQL 8.4: ANALYZE TABLE
• MySQL 8.4: LIMIT Query Optimization

Plan measurement, statistics, index trade-offs and LIMIT behavior were checked against the official MySQL 8.4 manual.