Missing composite index

MySQL · Query & indexing · L1

Prototype — data is mocked and actions are not connected yet.

← Query performance across the stack

MySQL Incident Query & indexing · L1 Passed Score 90%

A hot endpoint does a full table scan on orders because there's no composite index for its filter and sort.

Symptoms

One endpoint is slow under load while the rest of the app is fine. EXPLAIN shows type: ALL (full scan) and a large rows estimate on the orders table.

Root cause

The query filters by tenant_id and status and sorts by created_at, but no single index covers that combination, so MySQL scans the whole table and sorts in memory or on disk.

What you should do

  1. Reproduce with EXPLAIN and confirm type: ALL plus a Using filesort.
  2. Read the WHERE + ORDER BY + LIMIT shape of the query.
  3. Create a composite index (tenant_id, status, created_at) in that order.
  4. Re-run EXPLAIN and confirm a range/ref access and the filesort gone.

How it's simulated

Seed a multi-tenant orders table of several million rows and an endpoint filtering by tenant_id/status ordered by created_at, with the composite index removed.

Scoring

  • DetectRead EXPLAIN correctly and identified the full scan + filesort.
  • FixBuilt a composite index matching WHERE + ORDER BY in the right column order.
  • TrapDidn't just add single-column indexes that MySQL won't combine effectively here.
Back to goal

Prototype — scenario content is a preview; the live sandbox is not wired up.