Indexes & EXPLAIN
An index lets MySQL find matching rows without scanning the entire table — and EXPLAIN shows you, honestly, whether a given query is actually using one.
A query before indexing
SQL mysql shell
EXPLAIN SELECT * FROM products WHERE name = 'Widget';
Output
+----+-------------+----------+------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+----------+------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | products | ALL | NULL | NULL | NULL | NULL | 5000 | Using where | +----+-------------+----------+------+---------------+------+---------+------+------+-------------+ 1 row in set (0.00 sec)
The important columns here are type and rows: type: ALL means a full table scan — MySQL is reading every single row to check if it matches — and rows: 5000 is its estimate of how many it'll examine. On a small table that's harmless; on a large one, it's slow.
Adding an index
SQL mysql shell
CREATE INDEX idx_products_name ON products(name); EXPLAIN SELECT * FROM products WHERE name = 'Widget';
Output
+----+-------------+----------+------+--------------------+--------------------+---------+-------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+----------+------+--------------------+--------------------+---------+-------+------+-------+ | 1 | SIMPLE | products | ref | idx_products_name | idx_products_name | 402 | const | 1 | NULL | +----+-------------+----------+------+--------------------+--------------------+---------+-------+------+-------+ 1 row in set (0.00 sec)
After indexing, type changed from ALL to ref (an indexed lookup), key shows MySQL is actually using idx_products_name, and rows dropped from 5000 to 1 — MySQL now jumps straight to matching rows instead of checking every row in the table.
An index doesn't help if you wrap the column in a function:
WHERE YEAR(created_at) = 2024 can't use an index on created_at, because MySQL would have to compute YEAR() for every row before it can compare — forcing a full scan regardless of the index. Rewriting it as a range, WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01', lets the index actually get used.