Going Deeper: Professional SQL›Pro· 35 min read

Indexes & Reading EXPLAIN

Make slow queries fast by adding indexes — and use EXPLAIN to see whether MySQL is scanning the whole table or jumping straight to the rows it needs.

What you will learn

Explain what an index is and why it speeds up queries
Create an index on a frequently-searched column
Read EXPLAIN to tell a full scan from an indexed lookup

Why some queries are slow

Without help, MySQL answers WHERE email = 'asha@x.com' by reading every single row and checking each one — a full table scan. On 50 rows that is instant; on 5 million rows it is painfully slow. An index fixes this. Think of the index at the back of a textbook: instead of reading every page to find “photosynthesis”, you jump to the index, find the page number, and go straight there. A database index does exactly that for a column.

An index is a separate, sorted structure MySQL keeps for a column, letting it jump to matching rows instead of scanning all of them. Primary keys and UNIQUE columns are indexed automatically; you add indexes to the other columns you search or join on a lot.

Creating an index

Suppose your app constantly looks users up by email. You add an index on the email column:

Index the email column for fast lookups

CREATE INDEX idx_users_email ON users(email);

CREATE INDEX builds the sorted lookup structure; idx_users_email is just a name (a common convention is idx_table_column); ON users(email) says which table and column to index. From now on, WHERE email = ... queries can use this index to jump straight to the row instead of scanning the table.

Note: Output: Query OK, 0 rows affected. Records: 0 Duplicates: 0 Warnings: 0 The index is built. Lookups on email are now fast even as the table grows to millions of rows. You can also index two columns together for queries that filter on both — a composite index — with CREATE INDEX idx_name ON orders(user_id, created_at);.

EXPLAIN — see what MySQL is doing

How do you know whether a query uses an index or scans the whole table? Put EXPLAIN in front of it. EXPLAIN does not run the query — it shows MySQL’s plan: which table, whether an index is used, and roughly how many rows it must examine. Compare the same query before and after indexing.

First, before the index existed (or on an un-indexed column):

EXPLAIN on an un-indexed column

EXPLAIN SELECT * FROM users WHERE email = 'asha@x.com';

Note: Output (key columns): type key rows ALL NULL 500000 type: ALL means a full table scan — the slowest plan. key: NULL means no index was used. rows: 500000 is how many rows MySQL expects to read: the whole table. This is the signature of a slow query.

Now the same EXPLAIN after adding idx_users_email:

EXPLAIN on the now-indexed column

EXPLAIN SELECT * FROM users WHERE email = 'asha@x.com';

Note: Output (key columns): type key rows ref idxusersemail 1 Now type: ref (an indexed lookup, far better than ALL), key: idx_users_email shows the index is being used, and rows: 1 means MySQL expects to examine just one row instead of 500,000. That is the whole point of an index: from scanning everything to jumping to one row.

EXPLAIN says	Meaning
`type: ALL`	Full table scan — usually wants an index
`type: ref` / `eq_ref` / `const`	Using an index — good
`key: NULL`	No index used
`rows` (high)	Many rows examined — slow
`rows` (low)	Few rows examined — fast

Watch out: Indexes are not free: each one takes disk space and slightly slows down INSERT/UPDATE/DELETE (MySQL must keep the index updated too). So index the columns you search, join or sort on often — not every column. A handful of well-chosen indexes is the goal.

Tip: The professional workflow: when a query feels slow, run EXPLAIN on it. If you see type: ALL and a high rows count on a column you filter by, add an index on that column and run EXPLAIN again to confirm it dropped to an indexed lookup. This single skill is what employers mean by “can optimise queries”.

Q. In EXPLAIN output, what does type: ALL usually indicate?

Answer: type: ALL means a full table scan, where MySQL reads every row. On large tables that is slow — adding an index on the filtered column lets MySQL jump straight to matching rows (type: ref/const).

✍️ Practice

Add an index to a column you frequently filter by, then run EXPLAIN on a query that uses it.
Compare EXPLAIN output for a query on an indexed vs un-indexed column and note the type and rows differences.

🏠 Homework

Pick a query in your project database, run EXPLAIN, add a suitable index if it shows a full scan, and confirm the plan improves.