Reading EXPLAIN until a slow catalogue query gets fast

The slow query log from setting this server up had been running for months, and one query sat at the top of it the whole time: a filtered, sorted product listing taking 4.2 seconds against 60,000 rows. There was already an index covering the columns involved. It was never used, and EXPLAIN says exactly why if you read the right two columns.

The symptom

mysql> EXPLAIN SELECT p.id, p.name, p.price FROM products p
    ->  WHERE p.brand_id = 17 AND p.active = 1
    ->  ORDER BY p.price ASC LIMIT 24G

           id: 1
  select_type: SIMPLE
        table: p
         type: ALL
possible_keys: idx_active_price
          key: NULL
         rows: 58912
        Extra: Using where; Using filesort

Four things in that output matter. type: ALL means a full table scan. key: NULL means no index was used. possible_keys names one that could have been — so the optimiser considered it and rejected it. And Using filesort means the sort was done in a temporary buffer rather than read in order from an index.

Why it happens

The existing index was (active, price). The query filters on brand_id and active, then sorts by price. A composite index is a sorted list of concatenated column values, readable only from the left — so (active, price) can satisfy “active = 1” and then read price in order, but it has nothing to say about brand_id.

The optimiser therefore had a choice: use the index to get sorted rows and then filter 58,000 of them by brand, or scan the table and sort what survives. It estimated the scan was cheaper, and given those two options it was probably right.

The fix

Column order comes from the query, not the table

The rule is: equality conditions first, then the range or sort column. Everything after a range condition in the index is unusable for lookup, so the ordering is not a matter of taste.

-- equality, equality, then the sort column
ALTER TABLE products ADD INDEX idx_brand_active_price (brand_id, active, price);

         type: ref
possible_keys: idx_brand_active_price
          key: idx_brand_active_price
      key_len: 9
         rows: 412
        Extra: Using where

type: ref, 412 rows instead of 58,912, and Using filesort is gone — the index already holds the rows in price order within a brand, so the LIMIT 24 stops after twenty-four. Query time went from 4.2 seconds to 31 milliseconds.

Then make it covering

Thirty-one milliseconds is fine, but the query still performs a second read per row to fetch name, which is not in the index. Adding it means the index alone can answer the whole query.

ALTER TABLE products
  DROP INDEX idx_brand_active_price,
  ADD INDEX idx_brand_active_price_cover (brand_id, active, price, name);

         type: ref
          key: idx_brand_active_price_cover
         rows: 412
        Extra: Using where; Using index

Using index in Extra is the confirmation — the table itself was never touched. 31ms became 9ms. Note that Using index and Using index condition are different things; the second is a weaker optimisation and still reads rows.

The one that could not be fixed with an index

The same page had a second query, for the total count used by pagination, and no index arrangement helped it — because SQL_CALC_FOUND_ROWS forces every matching row to be evaluated, which is precisely what the LIMIT was avoiding.

-- one query, but LIMIT no longer short-circuits
SELECT SQL_CALC_FOUND_ROWS id, name, price FROM products
 WHERE brand_id = 17 AND active = 1 ORDER BY price LIMIT 24;
SELECT FOUND_ROWS();

-- two queries; the second is answered from the index alone
SELECT id, name, price FROM products
 WHERE brand_id = 17 AND active = 1 ORDER BY price LIMIT 24;
SELECT COUNT(*) FROM products WHERE brand_id = 17 AND active = 1;

The two-query version measured 9ms plus 3ms against 47ms for the single one. The instinct that one query must beat two is worth abandoning here: the COUNT(*) is answered entirely from the index, and the paged query gets to stop early.

When the index is right and still not used

One page kept scanning despite a perfectly good index, and no rearrangement helped. The optimiser was choosing correctly given what it believed about the table, and what it believed was six months out of date.

mysql> SHOW INDEX FROM products WHERE Key_name = 'idx_brand_active_price'G
  Column_name: brand_id
  Cardinality: 3          <-- there are 214 brands

mysql> ANALYZE TABLE products;
mysql> SHOW INDEX FROM products WHERE Key_name = 'idx_brand_active_price'G
  Cardinality: 209

Cardinality is how many distinct values the optimiser believes a column holds, and it decides how selective an index looks. At 3 the index appeared nearly useless, so a scan was the rational choice; at 209 the same index became obviously worth using and the plan changed with no schema change at all. Statistics go stale after a bulk import, which is exactly when someone notices the query got slow — and reaches for the index rather than for ANALYZE TABLE.

Verifying it worked

EXPLAIN proves the plan changed. The slow query log proves it changed for the traffic that actually arrives, which is the claim that matters.

$ mysqldumpslow -s t -t 5 /var/log/mysql/slow.log

# before — one week
Count: 4128  Time=4.19s (17296s)  products WHERE brand_id=N AND active=N

# after — one week
(no entries above 1.00s)

Seventeen thousand seconds of database time per week, gone. That figure is the one worth quoting, because it is the cost the old index arrangement was charging every week without anybody noticing it as a line item.

What this costs

Every index is written on every insert, update and delete, and this table now carries a 6.8 MB index it did not have. On a catalogue that is read constantly and written nightly, that is close to free. On an orders table it would not be.

The subtler cost is that the index is now coupled to this query. Change the sidebar so it sorts by name instead of price and the composite is wrong again, silently — the query still returns correct results, just slowly. Indexes are answers to specific questions, and they need revisiting whenever the question changes.