Black Friday, 10:12 AM: the shop shows a spinner. MySQL CPU hits 98%, PHP-FPM waits. Last release: five days ago. The team orders a VPS upgrade. At 11 AM, a developer runs EXPLAIN on checkout — type: ALL, rows: 2,400,000. Missing index on (status, created_at) since adding the "pending orders" filter.
A missing index does not shout. It accumulates until volume or traffic makes it audible. Good news: the signature is readable in slow log and EXPLAIN — if you look before buying hardware.
Signatures of a toxic query
In EXPLAIN, type: ALL means full table scan. Huge rows vs result means poor selectivity or missing index. Using filesort and Using temporary hurt on ORDER BY and GROUP BY. Repeated locks signal secondary contention.
Enable slow query log:
slow_query_log = 1
long_query_time = 1
log_queries_not_using_indexes = 1
When rows_examined / rows_sent > 1000 on an HTTP route, you have a priority candidate.
From slow query to useful index
Simplified example:
SELECT * FROM orders
WHERE status = 'pending' AND created_at > '2026-01-01'
ORDER BY created_at DESC LIMIT 50;
Without index: scan millions of rows. Fitting composite index:
CREATE INDEX idx_orders_status_created ON orders (status, created_at);
Column order: equalities first (status =), ranges next (created_at >), ORDER BY compatible when possible for covering scan.
Tools: slow log with mysqldumpslow, Performance Schema, APM linking route and query. Reproduce with production-like data — empty staging lies.
When not to index
On tables of a few thousand rows, scan stays fast. A nearly unique column already primary key needs no duplicate. On heavy writes, each index costs. Rare admin query prefers cache or denormalization.
On shared hosting, slow log may be inaccessible — "CPU limit reached" with no detail. On VPS, tune innodb_buffer_pool_size after indexes, not before.
The climax: hardware masks the query
Before any host upgrade for "slow site," demand top 3 slow queries from the last seven days. Often a ten-minute CREATE INDEX beats a hundred-euro VPS. See connection pooling and PostgreSQL EXPLAIN for the Postgres equivalent.
Decide and move forward without blind spots
Enable slow log in production with a one-second threshold and collect a week of data before any hardware decision. Run EXPLAIN on the five queries from critical routes — checkout, search, admin. Create a composite index, measure before and after under realistic load. Schedule quarterly review after large data import. Configure APM alert on rows_examined to catch drift before the next commercial peak.
Frequently asked questions
How do you spot a missing index?
Enable slow query log and sort by rows examined. EXPLAIN shows type ALL or non-selective index — high query time with rows_examined far above rows_sent.
Should you index every WHERE column?
No. Build composite indexes in order of most selective filters. Too many indexes slow INSERT and UPDATE.
Can an index become useless?
Yes — table growth, changed queries, or redundant leftmost-prefix index. Review after large import or search refactor.
Is adding RAM enough?
Temporarily. A full scan grows with data — buffer pool caches the scan once, not the structural cause.
Sites rarely fall without reason — they often fall on a query EXPLAIN can accuse in one line.