✏️ Explanatory Question
Level: Very Hard — Separates candidates who "add indexes" from those who understand HOW indexes are used internally.
orders(customer_id, status). EXPLAIN confirms it's being used (key = idx_cust_status), yet the query still reads millions of rows and is slow. The index is "used" but not "helping." Why, and how do you make it fast?
orders (20M rows)CREATE INDEX idx_cust_status ON orders(customer_id, status);
-- The query in question
SELECT customer_id, status, amount, created_at
FROM orders
WHERE customer_id = 42
AND status = 'completed'
AND amount > 1000;The cause: The index covers customer_id and status, so it narrows to matching rows. But amount and created_at are not in the index. For every matching index entry, MySQL must do a bookmark lookup — jump to the clustered index to fetch amount and created_at. Thousands of these random lookups are the bottleneck.
Using index in EXPLAIN.Using index condition.| Extra Value | Meaning | Good? |
|---|---|---|
| Using index | Covering index — answered from index alone | Best |
| Using index condition | ICP — extra filters applied at index level | Good |
| Using where | Rows fetched, then filtered (after lookup) | Weaker |
| Using filesort | Extra sort pass (no usable index for ORDER BY) | Bad |
-- BEFORE: index misses amount & created_at -> bookmark lookups
-- Extra: "Using index condition; Using where"
CREATE INDEX idx_cust_status ON orders(customer_id, status);
-- AFTER: covering index includes ALL columns the query touches
-- Filter columns first, then the SELECTed columns
CREATE INDEX idx_covering
ON orders(customer_id, status, amount, created_at);
-- Now the query is answered entirely from the index
-- Extra: "Using index" (no table access at all)
SELECT customer_id, status, amount, created_at
FROM orders
WHERE customer_id = 42
AND status = 'completed'
AND amount > 1000;
-- Confirm it
EXPLAIN SELECT customer_id, status, amount, created_at
FROM orders
WHERE customer_id = 42 AND status = 'completed' AND amount > 1000;SELECT * ruins covering indexes: A covering index only works if the index contains every selected column. SELECT * pulls all columns, so MySQL is forced back to the table for a lookup. Select only the columns you need to enable covering indexes.
-- Index: (last_name, first_name)
-- ICP pushes the first_name LIKE filter into the index scan,
-- avoiding lookups for rows that won't match
SELECT * FROM employees
WHERE last_name = 'Ansari'
AND first_name LIKE '%umm%';
-- EXPLAIN Extra: "Using index condition"amount and created_at to the index expensive?" → Yes — wider indexes cost more storage and slow writes. It's a trade-off: use covering indexes for hot, read-heavy queries where the speedup justifies the write cost. Don't blindly widen every index.