Investigating a slow order history query

Scope and assumptions

Assume MySQL 8.0 or 8.4 with InnoDB, an orders table, and application-provided customer_id. Example SQL omits sensitive data and is not tied to a real customer. Results differ by version, schema, data distribution, and existing indexes.

Example query

SELECT id, created_at, total FROM orders WHERE customer_id = 42 ORDER BY created_at DESC LIMIT 20;

Do not infer that the query is slow from SQL shape alone. First establish a real latency symptom and capture representative bind values and application timing.

Evidence to collect

• Exact SQL, parameter values, request frequency, and latency distribution from the application.
• MySQL version, storage engine, table row count, schema, and current indexes.
• Plain EXPLAIN output for common and selective customer values; note access type, selected key, estimated rows, and filesort indicators.
• Whether customer order counts are skewed and whether returned columns match the page’s actual needs.
• Concurrent workload and write rate, because every secondary index adds maintenance work.

Testable index hypothesis

If the current plan scans many orders or sorts a large candidate set, a composite index may help this access pattern:

CREATE INDEX idx_orders_customer_created ON orders (customer_id, created_at);

With equality on leading customer_id, MySQL may use the index to locate rows and may satisfy requested ordering depending on direction and optimizer choice. The index does not include total, so table lookups may remain. A wider covering index could reduce reads but increases storage and write cost; evaluate that tradeoff.

Safe verification

1. Test on a staging copy with representative row counts and customer distribution. Preserve the original query results for correctness comparison.
2. Compare EXPLAIN before and after, including chosen key, estimated rows, and sorting work.
3. Where acceptable, run EXPLAIN ANALYZE on the test environment. It executes the SELECT and adds instrumentation overhead.
4. Measure repeated runs across representative parameters and cache conditions. Compare latency distribution, rows examined, and resource use.
5. Include insert/update latency and index size in the decision. Use a controlled rollout and rollback plan for production changes.

Only report improvement after recording reproducible measurements with dataset, server version, hardware, cache conditions, and test method. This page contains no such measurements.

Why the hypothesis may fail

A customer with a large fraction of all orders may still make a scan reasonable. Another index may already cover the query. Sort elimination and optimizer estimates depend on server release and index definition. If selected columns are broad or the table is small, lookups or maintenance may outweigh savings. Check the actual plan.

Primary sources

• MySQL 8.4: Multiple-Column Indexes
• MySQL 8.4: EXPLAIN
• MySQL 8.4: Optimization and Indexes

Editorial review

Written and reviewed by the TuningLens editorial team. Published and updated 24 September 2026. Example is illustrative, without measured reproducible data.

TuningLens reviews SQL and submitted evidence. It does not connect to your database, apply index changes, or guarantee performance results.