The query that scans twelve million orders | Labs | DevOps in Practice

The order history endpoint exceeds its SLO after data growth. Database CPU is high, but only one query dominates the slow log.

Inspect the execution plan on the read replica before proposing an index.

Try: mysql -e 'EXPLAIN ANALYZE SELECT * FROM orders WHERE tenant_id=42 ORDER BY created_at DESC LIMIT 50;'

Terminal command

The query reads 50 rows instead of 12.4 million.

The composite index serves the equality filter and requested order without a filesort.

Lesson

Design indexes from observed access patterns, not individual columns. Put equality filters first, then ordering or range columns, verify with the real plan, and remember that every index adds write and storage cost.

Edit add_orders_tenant_created_index.sql

Create one index that matches the equality filter first and the requested order second.

Create idx_orders_tenant_created on orders (tenant_id, created_at DESC).

Key concepts

The execution plan is evidence

It shows how the database actually intends to answer a query.

What it is

EXPLAIN ANALYZE reports chosen access paths, row estimates, actual work, sorting, and timing.

In this incident

MySQL scans 12.4 million rows and performs a filesort to return only 50.

Why it matters

High query latency can come from work amplification even when the SQL looks simple.

Column order follows the access pattern

A composite index is useful from its leftmost columns.

What it is

An index on (tenant_id, created_at) groups rows by tenant and keeps each tenant's entries ordered by time.

In this incident

The query filters tenant_id with equality, then asks for newest rows.

Why it matters

An index on created_at alone still makes MySQL inspect other tenants' rows.

Indexes improve reads but tax writes

Every extra index consumes space and must be maintained.

What it is

INSERT, UPDATE, and DELETE operations update all affected secondary indexes.

In this incident

One targeted index replaces a tempting collection of overlapping indexes.

Why it matters

Verify the plan, monitor write latency, and remove redundant indexes deliberately.