Indexes: Why Your Query Is Slow Without Them

Your queries work correctly now. Eventually, some of them will get slow as tables grow, and indexes are the single biggest lever for fixing that.

What An Index Does

Without an index, MySQL answers WHERE email = 'tony@starkindustries.com' by checking every single row in the table, one by one, from the first to the last, called a full table scan. On a table with a hundred rows, that's instant. On a table with ten million rows, it can take seconds, which feels broken to a user waiting on a web page.

An index is a separate, sorted structure that lets MySQL jump almost directly to matching rows, similar to how a book's index lets you jump straight to a page instead of reading the whole book to find a topic. You create one like this:

CREATE INDEX idx_customers_email ON customers(email);

Now WHERE email = '...' can use that sorted structure to locate matches almost instantly, regardless of table size. Primary keys are automatically indexed; that's partly why looking up a row by id is always fast. Foreign key columns, as mentioned in an earlier lesson, usually get an automatic index too when declared properly with a FOREIGN KEY constraint.

Indexes Are Not Free

A genuinely common beginner mistake is thinking "more indexes is always better," and adding one to every column just in case. Every index speeds up reads on that column, but slows down every INSERT, UPDATE, and DELETE, because MySQL must also keep that index structure updated on every write. Index the columns you actually filter, join, or sort by regularly; don't index everything reflexively.

Checking With Explain

You can check whether MySQL is actually using an index with EXPLAIN, placed in front of any SELECT:

EXPLAIN SELECT * FROM customers WHERE email = 'tony@starkindustries.com';

The output's key column tells you which index, if any, MySQL chose. If it says NULL, no index was used, meaning a full table scan happened. The rows column estimates how many rows MySQL had to examine, a smaller number is better. You'll learn to read EXPLAIN output in full detail in a later lesson, but checking the key column alone already tells you a lot.

Composite Indexes

A composite index, covering multiple columns together, matters for queries filtering on more than one column at once:

CREATE INDEX idx_orders_customer_status ON orders(customer_id, status);

Order matters here. This index helps queries filtering by customer_id alone, or by customer_id and status together, but it does not help a query filtering by status alone, without customer_id. Think of it like a phone book sorted by last name then first name: you can quickly find all "Stark"s, and within that, quickly find "Tony Stark," but you cannot quickly find everyone named "Tony" across all last names using that same book.

A frequent mistake: adding %something% (a leading wildcard) in a LIKE query, which prevents MySQL from using a regular index efficiently, because it can't know where in the string the match might start, so it often falls back to scanning everything anyway. A search like LIKE 'Tony%' (wildcard only at the end) can use an index fine; LIKE '%Stark' generally cannot.

Another subtle trap: applying a function to an indexed column in your WHERE clause, like WHERE YEAR(placed_at) = 2026, usually prevents MySQL from using an index on placed_at, because it must compute YEAR() for every single row before it can compare. Rewriting it as a range, WHERE placed_at >= '2026-01-01' AND placed_at < '2027-01-01', lets the index work properly again.

A practical tip: don't guess which indexes you need upfront on a brand new project. Build your tables and queries first, then once you have realistic data volume, use EXPLAIN on your slowest or most frequent queries to find where an index would actually help. Premature indexing based on guesses often adds overhead for indexes that never get used.

Try It Yourself

A small challenge: create a table with at least a few thousand rows (you can generate test data with a loop in your app or a script), run EXPLAIN on a WHERE query before adding an index, then again after adding one, and compare the rows and key columns in both outputs.