MySQL Index Design: Reading EXPLAIN Through a Slow Query

Engineering notes with synthetic examples. Version scope and references are included in each article.

Scope: MySQL 8.0 / InnoDB. EXPLAIN ANALYZE requires 8.0.18 or later. The examples are illustrative and contain no production data or benchmark claims.

Slow-query optimization often goes wrong when type=ALL immediately triggers a new index, or a nonempty key field is treated as proof of success. The useful questions are how much data the database reads, how much it sorts to return a few dozen rows, and what the chosen index costs on the write path.

Start with the complete query requirement

Suppose an administrative page needs the latest 50 click events for an account after a specified time. Ordering by both event_time and id makes the order deterministic when timestamps are equal:

CREATE TABLE event_log (
  id BIGINT NOT NULL PRIMARY KEY,
  account_id BIGINT NOT NULL,
  event_type VARCHAR(16) NOT NULL,
  event_time DATETIME(3) NOT NULL,
  payload JSON,
  KEY idx_account_type_time (account_id, event_type, event_time, id)
) ENGINE=InnoDB;

SELECT id, event_time
FROM event_log
WHERE account_id = 42
  AND event_type = 'click'
  AND event_time >= '2026-01-01 00:00:00'
ORDER BY event_time DESC, id DESC
LIMIT 50;

This index order is a hypothesis to validate: constrain the account and event type, then search the time range. The queries a composite index can serve depend on column order and predicates; a composite index is not simply a collection of single-column indexes. See the MySQL documentation on multiple-column indexes for the leftmost-prefix rules.

Other queries need their own analysis. An endpoint filtering only by event_type across all accounts has a different access pattern. Conversely, if nearly every event for an account is a click, event_type may contribute little filtering. Whether to retain it also depends on other queries, index width, and write frequency.

Read the plan as a set of questions

Start with ordinary EXPLAIN to inspect the access path and estimated scan volume. Then run the following in a controlled environment:

EXPLAIN ANALYZE
SELECT id, event_time
FROM event_log
WHERE account_id = 42
  AND event_type = 'click'
  AND event_time >= '2026-01-01 00:00:00'
ORDER BY event_time DESC, id DESC
LIMIT 50;

EXPLAIN ANALYZE actually executes the statement and reports actual rows, timing, and loop counts. For an inner nested-loop operation, per-loop timing alone understates the total work. Parent and child timings must not be added mechanically either, because a parent’s execution includes its children. The version requirements and output semantics are documented in EXPLAIN .

I follow four questions through the execution tree: How much did the index narrow the candidate set? How many candidates were filtered out? How many rows were sorted? Did LIMIT stop the read early enough? When estimates and actuals diverge, I check parameter distributions, statistics, and skew before considering optimizer hints.

Using filesort indicates an additional sorting step; it does not by itself mean the sort spilled to disk. Whether an index can provide the required order also depends on the query. Judge the cost using row counts and actual time, as described in ORDER BY optimization .

Validate against the data distribution

One execution for an average account is insufficient. Include large accounts, small accounts, accounts with no results, and both narrow and wide time windows. A fast run against a warm cache does not establish cold-start or concurrent-read performance.

If the page also needs payload, do not put every field into an index merely to make it covering. Compare direct clustered-index lookups with fetching 50 primary keys and then loading the details. The latter adds a query and introduces consistency questions if data changes between the two reads. Add that complexity only when measurements identify row lookups as a significant cost.

Deep pagination needs a separate design. OFFSET 100000 asks the database to skip many results. Cursor pagination can return the last timestamp and ID and continue with a strict less-than predicate. This requires stable ordering keys and an explicit product decision about the visibility of inserts and deletes between pages.

Make the optimization reviewable

Keep the original SQL, representative parameters, table and index definitions, before-and-after plans, scan volumes, and latency percentiles under comparable load. Include write throughput, index size, and replication lag so that read improvements do not conceal regressions on the write path.

After rollout, monitor query digests rather than a single manually executed statement. Keep a rollback path if the benefit is unstable, and determine whether data distribution, plan selection, lock waits, or I/O explains the difference. A useful index optimization ends with an access-path decision supported by evidence.

Start with the complete query requirement

Read the plan as a set of questions

Validate against the data distribution

Make the optimization reviewable

References