MySQL Indexes: The 80/20 Guide to Queries That Don't Crawl

Backend Systems · August 26, 2026

Backend Systems MYSQ

MySQL indexes are the most powerful lever you have for query performance, and the easiest to get wrong. I have watched a single missing index turn a 50ms query into a 12-second one, and I have watched a well-placed index turn an unusable dashboard into a snappy one. The difference between a database that scales and one that does not usually comes down to whether the people writing the queries understand how the index is going to be used.

This article is the practical version. I will explain what an index actually is, how MySQL decides whether to use it, and the specific patterns that come up over and over in real applications. I am going to keep the theory minimal and focus on what to actually do.

What an index is, in one sentence

An index is a sorted copy of one or more columns, plus a pointer back to the original row. The database can search the sorted copy in O(log n) time instead of scanning the whole table. That is the entire idea.

If you have a table of 10 million orders, and you run SELECT * FROM orders WHERE user_id = 42 without an index on user_id, MySQL reads all 10 million rows and checks each one. With an index, it does a binary search on the sorted copy, finds the right entries in 3-4 lookups, and reads only those rows. The speedup is the difference between 8 seconds and 4 milliseconds.

This is why indexes matter. They turn linear scans into logarithmic lookups. Once you understand that, the rest of the article is variations on a theme.

The cost of indexes

Before you go adding indexes everywhere, understand the trade. Every index you add has three costs:

Storage: the index takes disk space, usually 10-30% of the indexed columns' size. Not huge, but not free.

Write performance: every INSERT, UPDATE, and DELETE has to update the index too. If you have 10 indexes on a table, every write touches 11 places (the row plus the 10 indexes). Tables with heavy writes and few reads can actually slow down with too many indexes.

Memory: MySQL caches index pages in the buffer pool. More indexes means less room for data caching, which can hurt overall performance.

The right number of indexes is the smallest set that makes your critical queries fast. Not zero. Not twenty. The smallest set that solves your actual workload. You measure. You add what is missing. You remove what is not used.

How to see whether your query uses an index

The EXPLAIN statement tells you what MySQL is actually doing. Run it before any query you are worried about:

EXPLAIN SELECT * FROM orders WHERE user_id = 42 AND status = 'paid';

The output has columns. The ones that matter:

type: this is the most important column. Values you want to see: const, eq_ref, ref, range. Values you do not want to see: ALL (full table scan), index (full index scan).

key: which index MySQL chose to use. If it says NULL, no index is being used. That is the bug to fix.

rows: the estimated number of rows MySQL will examine. A query that examines 4 rows is fast. A query that examines 4 million is slow.

Extra: extra info. Look for Using index (great, the query is covered by the index alone) or Using filesort (bad, the result is being sorted on disk).

Get in the habit of running EXPLAIN on every query you write, every query in a slow log, every query you are not sure about. It is the single most useful habit in MySQL performance work.

The five indexes you will actually need

For a typical web application, the indexes below cover 90% of cases. Start here. Add more only when EXPLAIN tells you a query needs them.

1. Primary key on id

You have this one for free if your table has an id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY column. The primary key is always an index. Make sure every table has one, and make sure it is auto-incrementing (so new rows always go to the end of the index, not somewhere in the middle).

2. Foreign keys

If you have user_id in the orders table, you almost always want an index on user_id. The most common query is "show me all orders for this user," and that query is the entire reason the column exists. Index it.

3. The columns you filter by

Any column that appears in a WHERE clause on a frequent query should be indexed. Common offenders: status, created_at, email, slug. If you write WHERE status = 'pending' more than a few times a day, index status.

4. The columns you sort by

If you have ORDER BY created_at DESC LIMIT 20, MySQL has to sort the results. If there is no index on created_at, the sort happens in memory (or on disk if the result set is large). With an index, the data is already in the right order. The query is dramatically faster. Same goes for any column you order by frequently.

5. The columns in composite conditions

If you filter by user_id = 42 AND status = 'paid', a single-column index on user_id or status alone is not ideal. MySQL will pick one, scan all the matching rows, and check the other condition manually. A composite index on (user_id, status) lets MySQL find the exact rows in one pass.

The order of columns in a composite index matters. The leftmost column is the most important. An index on (user_id, status) can be used for queries on user_id alone, or on user_id and status, but not for queries on status alone. If you also need to filter by status independently, add a separate index on status.

The three traps that bite everyone

Trap 1: Functions on indexed columns

SELECT * FROM users WHERE LOWER(email) = '[email protected]';

That LOWER() means MySQL cannot use an index on email. The function transforms every row, so the database has to scan and transform. The fix: store email lowercase, and query lowercase. Or use a generated column. But the simplest thing is to never wrap an indexed column in a function.

Trap 2: Leading wildcard in LIKE

SELECT * FROM articles WHERE title LIKE '%performance%';

-- can use an index

SELECT * FROM articles WHERE title LIKE '%performance';

-- cannot use an index

A leading wildcard means the index cannot be used for the search. The database has to scan every row and check the LIKE pattern. If you need full-text search, MySQL has FULLTEXT indexes. If you need prefix search (e.g., autocomplete), keep the wildcard at the end. If you really need both, use a full-text search engine like Elasticsearch or Meilisearch.

Trap 3: Implicit type conversion

SELECT * FROM users WHERE id = '42';

-- if id is INT, MySQL converts '42' to 42, index is used

SELECT * FROM users WHERE id = 42;

-- same result, but the index is definitely used

This one is subtle. MySQL is usually smart about converting strings to numbers for indexed numeric columns. But it is not always smart. If you have a string column and pass an integer, or vice versa, sometimes the conversion disables the index. Match your parameter types to your column types. Always.

How to find missing indexes in production

You do not have to guess. MySQL has a slow query log. Enable it, let it run for a day, look at the queries that show up. Most of them will be the same handful of patterns, and the missing index that would fix them is usually obvious from the WHERE clause.

You can also look at the sys.schema_index_statistics table. It tells you which indexes are being used and which are not. If you have an index that has never been used, drop it. Free performance for writes.

The maintenance routine

Indexes fragment over time, especially on tables with heavy writes. Run OPTIMIZE TABLE once a month on your busiest tables. It rebuilds the indexes and reclaims space. Do it during a low-traffic window; it can lock the table for a few minutes on large tables.

Also check for duplicate indexes. A common mistake is having an index on (user_id) and another on (user_id, status). The first one is now redundant (the second one covers both cases). MySQL will use the second one for both queries. Drop the first one.

What to skip

You do not need to read the MySQL internals manual. You do not need to become a query optimizer. You do not need to denormalize everything for performance. The 80/20 lives in the patterns above: index foreign keys, index the columns you filter and sort by, use composite indexes for multi-column conditions, run EXPLAIN on queries you care about, and clean up unused indexes once a quarter.

That is the whole game. Measure, index, verify with EXPLAIN, repeat. The performance you need is sitting in the queries you already have. Indexes are how you unlock it.

Frequently asked questions

How do I know if my query needs an index?

Run EXPLAIN on it. If the type column says ALL or index, MySQL is scanning more rows than it should. If key is NULL, no index is being used at all. Those are the two signals that an index would help.

How many indexes is too many?

There is no hard limit, but the rule of thumb is: index only what your queries actually use. If a column never appears in a WHERE, ORDER BY, or JOIN clause on a real query, do not index it. The write cost is not worth the unused read benefit.

Should I use UUIDs or auto-increment for primary keys?

Auto-increment is faster and uses less storage. UUIDs are better for distributed systems where multiple servers need to generate IDs without coordinating. If you do not have a specific reason for UUIDs, use auto-increment and keep it simple.

← More in Backend Systems