Indexing and Query Tuning

The /api/orders endpoint has a p95 of 240 ms. The slow query log names the culprit: a SELECT filtered by customer_id and ordered by created_at. Before you touch an index, you make the planner show its work:

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

The result is one row per table in the join, and four columns carry almost all the meaning — and once the compile step is gone (Enabling OPcache), a query like this is usually the next latency wall your application meets:

The type ladder is worth memorizing in descending quality: const (single row via primary/unique), eq_ref (one row per input row — a join on a primary key), ref (all matching rows for one value), range (index scan over a bounded range), index (full index scan), ALL (full table scan). Your query is at the bottom: MySQL reads all 2.1 million rows to return 20.

Now add the index the predicate and sort actually ask for, and re-run the same EXPLAIN:

ALTER TABLE orders ADD INDEX idx_orders_customer_created (customer_id, created_at);

EXPLAIN SELECT id, status, total, created_at
  FROM orders WHERE customer_id = 42 ORDER BY created_at DESC LIMIT 20;
-- type=ref  key=idx_orders_customer_created  rows=24  Extra=NULL

Same statement, different plan: ref access, roughly 24 rows examined, and no filesort because the B-tree already holds each customer's rows in created_at order. That is the entire mechanism of query tuning — an index is a pre-sorted structure the planner can walk instead of reading the table, and EXPLAIN is how you prove the plan changed rather than hoping. Two Extra values deserve their own recognition: Using temporary; Using filesort together means the sort spilled to a temporary table — common with GROUP BY over unindexed columns — and Using index condition means index condition pushdown filtered rows inside the index before row lookups, cheaper than a full fetch but not as good as a covering scan.

Estimates are estimates. EXPLAIN ANALYZE (MySQL 8.0.18+) actually executes the query and reports reality next to the plan: actual time=0.08..91.4 rows=20 loops=1. When rows=24 is estimated but thousands are examined, InnoDB's table statistics are stale — ANALYZE TABLE orders refreshes them. PostgreSQL has always had EXPLAIN ANALYZE in this role, with actual rows vs plan rows per node.

Two support commands complete the toolkit. Refresh stale statistics explicitly:

ANALYZE TABLE orders;

And turn guessing into a work queue with the slow query log:

slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 0.5

mysqldumpslow -s t /var/log/mysql/slow.log then ranks statements by total time, so you tune what production actually runs rather than what you assume runs — and each fix carries its before/after EXPLAIN pair into the pull request, where the evidence travels with the change.

The discipline to state in interviews: never add an index you have not EXPLAINed before and after. Before proves the plan is bad and pins the baseline; after proves the planner adopted it and shows the new row estimate. Turn on the slow query log while you work — long_query_time = 0.5 plus mysqldumpslow -s t ranks offenders so you tune what production actually runs, not what you assume runs.

A composite index is one B-tree sorted by the first column, ties broken by the second, then the third. The phone-book analogy holds exactly: a book sorted by last name then first name finds "Nguyen, An" quickly, but cannot find every "An" without reading the whole book. That is the leftmost-prefix rule: a composite index can only serve predicates that constrain its columns from the leftmost one onward, contiguously.

Take INDEX (customer_id, status, created_at) and watch which queries it can satisfy:

Two design rules fall out. First, put equality-tested columns before range-or-sort columns: a range condition stops the index from constraining the columns after it, so (customer_id, created_at, status) would lose status for any range query — created_at must sit last if it carries a > predicate. Second, when the predicate pins the prefix and the ORDER BY names the next column, the index serves both and the filesort disappears — that combination is why one good composite index often fixes both the scan and the sort you saw in Chapter 1.

The covering-index bonus: if every column in your SELECT list is in the index — remembering that InnoDB secondary indexes silently append the primary key — MySQL answers from the index alone. SELECT id, status FROM orders WHERE customer_id = 5 against (customer_id, status) is covering; EXPLAIN says Extra: Using index and no row is ever read from the clustered tree. This is why selecting * is a performance decision, not a style preference: one extra column can turn an index-only scan into millions of row lookups.

One worked example, because index design is a design skill. The orders endpoint filters WHERE customer_id = ? AND status = ?, sorts by created_at DESC, and selects id, status, total, created_at. The index (customer_id, status, created_at) serves both equalities as its prefix and the sort as its third column; total is not in the index, so each of the 20 matches costs one clustered-tree lookup — a deliberate trade at 20 rows per request and fatal at millions. Naming the index after the query it serves (idx_orders_customer_status_created) is how the schema stays explainable two years later, when nobody remembers why an index exists well enough to drop it.

Indexes are not free, and this is where candidates lose points. Every index on orders must be updated on each insert and each update of its columns, and every index consumes buffer-pool memory that could hold data. A table with nine indexes pays that cost nine times per write. That is why the correct workflow is slow-log-first: add the indexes your measured queries need, EXPLAIN each one, and drop the ones EXPLAIN shows are never chosen.

Also know why one composite beats two single-column indexes: MySQL uses at most one index per table per query (index merge exists but rarely wins), so WHERE customer_id = 5 AND status = 'paid' against separate (customer_id) and (status) indexes falls back to merging or scanning, while the composite (customer_id, status) serves it as one walk. Your Eloquent where('customer_id')->where('status') generates the same predicate either way — the index design stays your job.

Two application-level patterns destroy performance while every individual query looks innocent in the log. The first is the N+1: a controller lists 50 orders, and the Blade template touches $order->customer->name in the loop, firing one SELECT per order. Fifty fast queries at ~2 ms each still cost 100 ms of round trips.

Count queries per request to see it — DB::listen(), Laravel Debugbar's query tab, or Telescope all expose the number instantly — then fix it with one eager load:

$orders = Order::with('customer')
    ->where('customer_id', $id)
    ->latest('created_at')
    ->limit(20)
    ->get();
// 2 queries total: 20 orders + 1 WHERE id IN (...) for their customers

The second pattern is deep pagination. LIMIT 20 OFFSET 100000 makes MySQL walk and discard 100,000 rows before returning the ones you want — cost grows with the offset, so page 5,000 is 5,000× the work of page 1. Keyset pagination replaces the offset with a cursor: WHERE created_at < :last_seen ORDER BY created_at DESC LIMIT 20. Cost stays flat because the index jumps straight to the cursor position.

When you need the planner's reasoning rather than its summary, ask for the cost estimates:

EXPLAIN FORMAT=JSON SELECT ...

The JSON includes "cost_info": { "query_cost": "1543.22" }, the chosen "key", and per-scan row counts. Comparing two candidate plans' query costs — composite versus index merge, for instance — turns "I added an index" into "the planner's cost for this statement dropped from 1543 to 12.4," which is the currency these conversations run on. optimizer_trace goes one level deeper into why a plan was rejected; reach for it when the planner ignores an index that looks perfect.

Normalization deserves a straight answer because interviewers ask it directly. Removing duplication (customer data living in customers, not copied into orders) keeps updates consistent, but the price is joins — and every join needs an index on its key: orders.customer_id → customers.id is an eq_ref join because the primary key is indexed, while an unindexed foreign key drags both tables into a scan. Denormalizing customer_name onto orders trades that join for write-time bugs (which copy do you update?); do it only when EXPLAIN and a profile prove the join is the bottleneck and you have a rebuild path. Prepared statements round out the picture: use real prepares (ATTR_EMULATE_PREPARES = false) so the server plans and caches by statement shape — and note that MySQL 8 removed the query cache entirely, so "the database cached it" is never the explanation for a fast query anymore.

A join earns one concrete number so normalization stops being abstract. SELECT o.id, c.name FROM orders o JOIN customers c ON c.id = o.customer_id — with the primary key indexed, EXPLAIN reports eq_ref and reads exactly one row per order; with customers.id unindexed, that same join degrades toward scanning customers for every input row. Every foreign key therefore needs an index on the side you query by, and a migration that adds a FK column without one is a recurring source of production regressions weeks later. Eloquent's with('customer') emits precisely this shape — one query for orders, one WHERE id IN (...) for customers — and whether it lands as eq_ref or ALL is decided by the indexes from Chapter 2, not by the framework.

The step beyond single-query tuning is finding which queries matter at all — that is Profiling Slow Requests, where a request's 40 statements get ranked by the time they actually consume.