SQL Indexes and EXPLAIN: Query Optimization

On 12 rows every query is instant. On 12 million rows, the same query can take seconds – or milliseconds – depending on one thing: whether the database can use an index. Indexes are the most important performance tool in SQL, and "how would you speed up this query?" is a standard question for backend and data roles.

What an index is

An index is a separate, sorted structure (a B-tree) that maps column values to rows – like the index at the back of a book. Without one, finding orders from 1 October means reading every row (a full table scan). With an index on order_date, the database jumps straight to the first matching entry and reads forward.

A B-tree lookup takes only a few page reads even for millions of rows – roughly log₂(n) comparisons, about 24 for 16 million rows.

Creating indexes

CREATE INDEX idx_orders_date ON orders (order_date);
CREATE INDEX idx_orders_customer_status ON orders (customer_id, status);   -- composite
CREATE UNIQUE INDEX uq_customers_email ON customers (email);              -- also enforces uniqueness

SHOW INDEX FROM orders;

Primary keys and UNIQUE constraints create indexes automatically, and InnoDB indexes foreign key columns. Add your own indexes for columns you often filter, join or sort on.

Reading EXPLAIN

Put EXPLAIN before a SELECT to see the plan the optimiser chose, without running the query. These plans come from a copy of the orders table with 1,000,000 rows (trimmed to the key columns):

EXPLAIN SELECT order_id, status FROM orders WHERE order_date >= '2025-10-01' AND order_date < '2025-10-08';

+--------+------+---------------+------+--------+----------+-------------+
| table  | type | possible_keys | key  | rows   | filtered | Extra       |
+--------+------+---------------+------+--------+----------+-------------+
| orders | ALL  | NULL          | NULL | 998214 |    11.11 | Using where |
+--------+------+---------------+------+--------+----------+-------------+

+--------+-------+-----------------+-----------------+------+----------+-----------------------+
| table  | type  | possible_keys   | key             | rows | filtered | Extra                 |
+--------+-------+-----------------+-----------------+------+----------+-----------------------+
| orders | range | idx_orders_date | idx_orders_date | 4180 |   100.00 | Using index condition |
+--------+-------+-----------------+-----------------+------+----------+-----------------------+

MySQL 8 also has EXPLAIN ANALYZE, which runs the query and reports real timings for each step.

Composite indexes and the leftmost-prefix rule

An index on (customer_id, status) is sorted by customer first, then by status within each customer – like a phone book sorted by surname, then first name.

Put the column used with = first and range or sort columns after it. One well-chosen composite index often replaces several single-column ones.

Covering indexes

If an index contains every column a query needs, MySQL answers from the index alone without visiting the table ("Using index" in EXPLAIN). In InnoDB every secondary index also stores the primary key, so an index on (customer_id, status) covers SELECT order_id, status FROM orders WHERE customer_id = 1.

What stops an index being used

-- Same rows, but only the second form can use idx_orders_date
SELECT COUNT(*) AS by_function FROM orders WHERE MONTH(order_date) = 10 AND YEAR(order_date) = 2025;
SELECT COUNT(*) AS by_range    FROM orders WHERE order_date >= '2025-10-01' AND order_date < '2025-11-01';

+-------------+
| by_function |
+-------------+
|           3 |
+-------------+
+----------+
| by_range |
+----------+
|        3 |
+----------+

The cost of indexes

Indexes aren't free: every INSERT, UPDATE and DELETE must update each index, and they use disk and memory. Index what your real queries need (check the slow query log), drop unused ones, and don't index every column "just in case".

Practice

Summary

Interview practice: indexing and query optimisation questions.