Indexes

As StreamHub's tables grow from a handful of rows to millions, a query like "find every movie released in 2010" could, in theory, force MySQL to check every single row in movies one by one. An index is what prevents that. This lesson covers what indexes are, how to create the right kind for a given query, and - just as importantly - why you can't simply index every column and call it a day.

What an Index Actually Is

An index is a separate, ordered data structure that MySQL maintains alongside a table, built from the values in one or more columns. Instead of scanning every row to find matches, MySQL can use the index to jump straight to the relevant rows. A useful analogy: a book's index at the back lets you jump straight to the page covering "normalization" instead of reading every single page from the start looking for it. A table index does the same thing for a column's values.

Primary Keys Are Indexed Automatically

As covered in Primary & Foreign Keys, MySQL automatically creates an index on every PRIMARY KEY column - that's part of why looking up a single row by its id is fast by default. What MySQL does not do automatically is index foreign key columns, or any other column you happen to filter or sort by frequently - those indexes are something you have to create yourself, deliberately, based on how your queries actually use the data.

CREATE INDEX - Indexing a Single Column

Suppose StreamHub frequently runs queries that filter or sort movies by their release year - a "browse by decade" feature, for example. Without an index, each of those queries would scan the entire movies table. Adding an index on release_year fixes that:

CREATE INDEX idx_movies_release_year ON movies(release_year);

This is worth adding specifically because the column is frequently used in a WHERE or ORDER BY clause - an index on a column that's rarely queried directly wouldn't earn its keep, since it still costs MySQL extra work to maintain with no corresponding read benefit.

Composite Indexes - Indexing Multiple Columns Together

A composite index spans more than one column, and is built for queries that filter on that same combination together. StreamHub often needs "all reviews for a given movie, with a minimum rating" - a query that filters on both movie_id and rating at once:

CREATE INDEX idx_reviews_movie_rating ON reviews(movie_id, rating);

Column order matters here, and it's an easy detail to get wrong. MySQL can use a composite index for a query that filters on movie_id alone, or on movie_id combined with rating - matching a leftmost prefix of the index - but it generally can't use this same index efficiently for a query that filters on rating alone, skipping straight to the second column. If lookups by rating alone were also common, that would call for its own separate index.

UNIQUE Indexes

A unique index does everything a normal index does, plus it enforces that no two rows can share the same value in that column. As covered in Constraints, this is exactly what happens under the hood when you declare a column UNIQUE - MySQL creates a unique index automatically to enforce it, rather than checking every existing row by hand on each insert:

-- This UNIQUE constraint on users.email is backed by a unique index automatically
email VARCHAR(255) UNIQUE

Viewing and Dropping Indexes

SHOW INDEX lists every index currently defined on a table, including the automatic ones from primary and unique keys:

SHOW INDEX FROM movies;

To remove an index that's no longer useful, DROP INDEX takes the index name and the table it belongs to:

DROP INDEX idx_movies_release_year ON movies;

The Real Tradeoff - Indexes Aren't Free

Indexes speed up reads, but that speed isn't free - it's paid for elsewhere. Every INSERT, UPDATE, or DELETE on an indexed column has to update the index's data structure too, not just the table row itself, which makes writes slower the more indexes a table carries. Indexes also take up additional disk space, sometimes substantial amounts on a large table. This is why indexing every column "just in case" is a genuine anti-pattern rather than free performance - a table with ten unnecessary indexes pays a write-speed and storage cost on every single insert, for read benefits that may never actually be used.

Deciding which columns actually deserve an index is best driven by looking at your real, slow queries rather than guessing - Query Optimization & EXPLAIN, up next, covers exactly how to find them.