MySQL indexing for fast search on millions of rows

A query that takes 5 milliseconds on 10,000 rows can take 5 seconds on 10 million. Almost always the difference is indexing. On FilterCrossHub, where search runs across more than 7 million parts records, getting indexes right was what made results come back instantly. These are the ideas I rely on.

What an index actually is

A MySQL (InnoDB) index is a sorted copy of one or more columns, stored as a B-tree, with a pointer back to the full row. Without one, finding a value means reading every row. With one, MySQL walks down the tree in a few steps, the same way you'd find a word in a dictionary without reading every page.

Start with EXPLAIN

Before adding anything, ask MySQL how it plans to run the slow query:

EXPLAIN SELECT id, part_number, manufacturer
FROM parts
WHERE normalised_number = 'LF9000';

The columns worth reading first:

• type: ALL means a full table scan. You want ref, eq_ref or const.
• key: the index it chose. NULL means none.
• rows: how many rows MySQL expects to examine. This should be close to the number you get back, not the size of the table.
• Extra: Using filesort or Using temporary on a big result is a warning sign.

Composite indexes and the leftmost rule

When a query filters on several columns, one index across all of them beats several single-column indexes. Column order matters: MySQL can use the index from the left, but it can't skip a column.

CREATE INDEX idx_parts_mfr_cat ON parts (manufacturer_id, category_id, created_at);

-- Uses the index
WHERE manufacturer_id = 12
WHERE manufacturer_id = 12 AND category_id = 4
WHERE manufacturer_id = 12 AND category_id = 4 ORDER BY created_at

-- Can't use it: skips the first column
WHERE category_id = 4

A good rule of thumb: equality filters first, then range filters or sort columns last. That lets the same index handle both the WHERE and the ORDER BY, and the filesort disappears.

Covering indexes: skip the table entirely

If every column a query needs is inside the index, MySQL never has to read the row itself. EXPLAIN shows Using index. For a hot search that returns a few columns, this can be a big win.

CREATE INDEX idx_parts_search ON parts (normalised_number, id, manufacturer_id);

This is also a good reason to avoid SELECT * on search queries.

Things that quietly disable an index

• Functions on the column. WHERE UPPER(part_number) = 'LF9000' can't use an index on part_number. Store a normalised column and index that instead.
• Leading wildcards. LIKE '%9000' scans everything. LIKE 'LF9%' can use the index. For real "contains" search, use a FULLTEXT index or a search engine.
• Type mismatches. Comparing a VARCHAR column with a number forces a conversion on every row.
• OR across different columns. Often better written as a UNION of two indexed queries.

Don't index everything

Every index speeds up reads and slows down writes, because each insert and update has to maintain it. On a table that receives bulk imports, that cost is real. I add indexes for the queries that actually run, check them with EXPLAIN, and remove ones that sys.schema_unused_indexes shows nobody uses.

Checklist

• Run EXPLAIN on every slow query before changing anything
• Build composite indexes in the order the query filters: equality first, range and sort last
• Use covering indexes for hot, narrow queries
• Never wrap an indexed column in a function; store a normalised copy
• Avoid leading wildcards; use FULLTEXT for "contains" search
• Remove indexes nothing uses

Search getting slower as your data grows? Send me the details.