Query Optimization & EXPLAIN

Adding an index, as covered in Indexes, only helps if MySQL actually chooses to use it. Rather than guessing whether a query is fast or slow, MySQL lets you ask it directly: "how would you run this?" That's exactly what the EXPLAIN statement does - it hands back MySQL's execution plan for a query without ever running the query itself. Reading that plan is the single most useful skill for figuring out why a StreamHub query is slow and, more importantly, what to do about it.

EXPLAIN - Seeing the Plan Without Running the Query

Put EXPLAIN in front of any SELECT (or UPDATE/DELETE) statement and MySQL returns a description of the plan it would use, instead of the query's actual results. Nothing is executed against the table - you only see MySQL's intentions.

EXPLAIN SELECT * FROM reviews WHERE movie_id = 5;

Here's the kind of output that comes back, one row per table involved in the query:

Even without knowing every column yet, this already tells a story: MySQL has no possible_keys to choose from, so it isn't using any index (key is NULL), and it expects to examine 48,210 rows just to find the ones where movie_id = 5. That's the entire reviews table, checked row by row.

The type Column - How MySQL Reaches Each Row

The type column is usually the first thing worth checking - it describes the access method MySQL will use to find matching rows in a table. A few values come up constantly:

There's no universal rule that says "never use ALL" - a lookup table with 12 rows will happily scan the whole thing faster than an index lookup would even set up. The concern is an ALL scan on a table that's large and only getting larger, like reviews in a StreamHub with millions of ratings.

The rows Column - MySQL's Estimate, Not a Guarantee

The rows column is MySQL's estimate of how many rows it will have to examine to satisfy the query - based on internal statistics about the table, not an exact count. It's still extremely useful: a huge rows estimate on a query that's supposed to return a small, filtered result is a strong signal that MySQL is working far harder than it should be, almost always because there's no useful index for it to use, exactly as covered in Indexes.

Before and After - Adding the Missing Index

Let's follow the reviews query from the top of this page all the way through. Before any index exists on movie_id, EXPLAIN shows exactly what we saw above - a full scan:

EXPLAIN SELECT * FROM reviews WHERE movie_id = 5;

Now add an index on the column the query filters by:

CREATE INDEX idx_reviews_movie_id ON reviews(movie_id);

Running the exact same EXPLAIN again tells a completely different story:

type went from ALL to ref, key now shows the index actually being used, and the rows estimate dropped from 48,210 to 32 - MySQL expects to examine only the handful of reviews that actually belong to movie 5, instead of scanning the whole table to find them.

EXPLAIN ANALYZE - Actual Execution Statistics

EXPLAIN on its own only shows the planner's estimate - it never actually runs the query. Newer versions of MySQL also support EXPLAIN ANALYZE, which does run the query and reports real timing and row counts alongside the plan, letting you compare what MySQL expected against what actually happened.

EXPLAIN ANALYZE SELECT * FROM reviews WHERE movie_id = 5;

Because it genuinely executes the query, reach for EXPLAIN ANALYZE once you've already narrowed things down with plain EXPLAIN - it's more informative, but running it against a heavy, unfiltered query on a huge table costs real time and real database load.

Writing Queries That Are Easy to Optimize

A few habits consistently keep MySQL's planner able to do its best work:

• Select only the columns you need. SELECT * is convenient while exploring, but pulling every column - including large text ones like reviews.comment - means more data read and transferred than the task actually requires.
• Don't wrap an indexed column in a function inside WHERE. This is one of the most common real-world performance mistakes, and it's worth understanding exactly why it happens.

The Function-Around-a-Column Gotcha

Imagine reviews.review_date has an index on it, and you want every review written in 2024. It seems natural to write:

-- Looks reasonable, but defeats the index on review_date
SELECT * FROM reviews
WHERE YEAR(review_date) = 2024;

The problem: MySQL's index on review_date stores the raw date values themselves, in order. To evaluate YEAR(review_date), MySQL would have to compute the function's result for every single row before it can compare anything to 2024 - which means it has to read every row anyway, so the index can't help at all. Wrapping an indexed column in a function almost always forces a full scan, even when an index exists.

The fix is to rewrite the condition as a plain range comparison directly on the column, with no function wrapped around it:

-- Indexable: compares review_date directly, no function wrapping it
SELECT * FROM reviews
WHERE review_date >= '2024-01-01'
  AND review_date < '2025-01-01';

This expresses exactly the same condition - "review_date falls somewhere in 2024" - but because review_date now appears bare on both sides of the comparison, MySQL can use the index to jump straight to the matching range instead of evaluating a function against every row.