Indexing and Query Optimization

What an index is#

An index is a separate, sorted structure (a B-tree in InnoDB) that lets MySQL find rows without reading the whole table. Think of the index at the back of a book: sorted terms pointing at pages.

              [ 40 | 80 ]
             /     |      \
     [10 20 30] [50 60 70] [90 95 99]   ◀ leaf pages, sorted
        │  │  │
       row pointers (primary key values)

Finding 60 takes three page reads instead of scanning everything. In InnoDB, secondary index leaves store the primary key, so a lookup goes index, then primary key, then the row. That second hop is why a compact primary key matters.

Creating indexes#

CREATE INDEX idx_orders_customer ON orders (customer_id);
CREATE INDEX idx_orders_cust_date ON orders (customer_id, ordered_at);
SHOW INDEX FROM orders;

Indexes speed up reads and slow down writes, since every insert and update must maintain each one. They also take disk and memory. Add the ones your queries need, not one per column.

Composite indexes and the leftmost prefix#

An index on (customer_id, ordered_at) is sorted by customer_id first, then by ordered_at inside each customer.

• WHERE customer_id = 1 uses it.
• WHERE customer_id = 1 AND ordered_at > '2025-02-01' uses both columns.
• WHERE ordered_at > '2025-02-01' alone can't use it efficiently. The date isn't the leading column.

Put equality columns first, then the range or sort column. Column order is the whole game.

What breaks an index#

WHERE YEAR(ordered_at) = 2025       -- function on column
WHERE email LIKE '%@example.com'    -- leading wildcard
WHERE customer_id = '1'             -- type mismatch can force conversion
WHERE status <> 'new'               -- low selectivity, often a scan anyway

Low-cardinality columns like a 3-value status are poor index candidates alone. If an index would match most rows, the optimizer ignores it, and it's right to.

Reading EXPLAIN#

EXPLAIN SELECT * FROM orders WHERE customer_id = 1 ORDER BY ordered_at;
EXPLAIN ANALYZE SELECT ...;   -- actually runs it, shows real timings (8.0.18+)

Columns that matter:

A covering index contains every column the query needs, so MySQL never touches the table. Selecting only customer_id, ordered_at with the composite index above is covered.

Full-text indexes#

ALTER TABLE products ADD FULLTEXT KEY ft_name (name);
SELECT name FROM products
WHERE MATCH(name) AGAINST('wireless mouse' IN NATURAL LANGUAGE MODE);

Use it for word search. LIKE '%word%' can't use a normal index.

Common mistakes#

• Indexing every column "just in case".
• Wrong column order in a composite index.
• Wrapping the column in a function.
• Never looking at EXPLAIN, and guessing instead.

Practice#

Fill orders with 200,000 rows. Run EXPLAIN on WHERE customer_id = 500 AND ordered_at >= '2025-02-01' with no index, then add (customer_id, ordered_at) and compare type, key, and rows. Then reverse the column order and check what changes.