Query Cost and EXPLAIN

---

title: “Query Cost and EXPLAIN” part: 3 topic_number: 7 slug: “query-cost-explain” difficulty: “Advanced” prerequisites: “select-from-where, inner-join” —

Query Cost and EXPLAIN

What Is Query Cost?

Every query has a cost — how much work MySQL has to do to get your answer.

The same result can be cheap (reads 3 rows) or expensive (reads 500,000 rows), depending on how the query is written. EXPLAIN lets you see exactly what MySQL plans to do — before it does it.

---

First — What Is an Index?

Before reading anything else on this page, you need to know what an index is.

Think of a phone book. If you want to find “Smith, John”, you don’t read every name from page 1. You jump to the S section and find him in seconds. The alphabetical ordering is the index — it lets you skip to the right place.

A database index works the same way. When MySQL has an index on last_name, it can jump straight to all the Smiths instead of reading every employee row one by one.

Without an index: MySQL reads every row in the table, checks each one, keeps the matches.
With an index: MySQL jumps directly to the rows that match, reads only those.

On a table with 20 rows the difference is invisible. On a table with 500,000 rows it’s the difference between a query that takes 0.001 seconds and one that takes 8 seconds.

Primary keys and foreign keys already have indexes — MySQL creates them automatically. But regular columns like last_name or salary don’t get one unless you add it yourself:

ALTER TABLE employees ADD INDEX idx_last_name (last_name);

This creates a sorted lookup structure on last_name. You only need to do it once. After that, any query filtering by last_name uses it automatically.

---

Running EXPLAIN

Add EXPLAIN before any SELECT to see what MySQL plans to do:

EXPLAIN
SELECT first_name, last_name
FROM employees
WHERE last_name = 'Smith';

You get a table of output. There are many columns — you only need to look at three.

---

The Three Columns That Matter

1. type — How is MySQL finding the rows?

This tells you whether MySQL is using an index or reading the whole table:

type	What it means	OK?
ALL	No index — reads every row in the table from start to finish	❌ Bad on large tables
range	Has an index, reads a slice of it (e.g. WHERE salary > 50000)	✅ Fine
ref	Has an index, jumps to all rows matching a specific value	✅ Good
const	Has an index, finds exactly one row (e.g. WHERE id = 5)	✅ Best

ALL means no index was used. MySQL is doing the phone book equivalent of reading every name from page 1. On large tables, this is almost always the problem.

---

2. key — Which index did MySQL use?

• NULL → no index used — MySQL is doing a full table scan
• An index name → MySQL found the index and is using it

key = NULL          ← no index, full scan
key = PRIMARY       ← using the primary key index
key = idx_last_name ← using your custom index

If type = ALL and key = NULL on the same row, that’s a full table scan with no index — the combination to fix.

---

3. rows — How many rows is MySQL scanning?

This is MySQL’s estimate of rows it needs to read to get your answer.

rows = 3 is great. rows = 847291 means MySQL is doing a lot of unnecessary reading.

---

A Before and After Example

EXPLAIN
SELECT * FROM employees WHERE last_name = 'Smith';

Before an index on last_name:

type	key	rows
ALL	NULL	20

MySQL reads all 20 rows, checking each last_name. No problem on 20 rows — but on 500,000 employees it reads 500,000 rows every time someone searches by last name.

-- Create an index on last_name
ALTER TABLE employees ADD INDEX idx_last_name (last_name);

-- Run EXPLAIN again
EXPLAIN
SELECT * FROM employees WHERE last_name = 'Smith';

After adding the index:

type	key	rows
ref	idx_last_name	1

MySQL now jumps straight to ‘Smith’. type changed from ALL to ref. rows dropped from 20 to 1. On a 500,000-row table, this would drop from 500,000 rows read to a handful.

---

Checking Query Cost in MySQL Workbench

Workbench has a visual version of EXPLAIN — boxes and arrows instead of a table, with colours to show which parts are expensive. Here’s how to use it:

Step 1: Write your SELECT query in the query editor. Do not add EXPLAIN yourself.

Step 2: Click the lightning bolt with magnifying glass icon (⚡🔍) in the toolbar above the editor. This runs EXPLAIN on your query without actually executing it.

Step 3: A diagram appears. Each box is one operation MySQL performs (a table scan, an index lookup, a join, etc.). Arrows show which operations feed into which.

The boxes are colour-coded by cost:

• 🟦 Blue — low cost, nothing to worry about
• 🟧 Orange — moderate cost, worth noting
• 🔴 Red — high cost, this is the expensive part

Step 4: Click the red or orange box to see its details — it shows the operation type, estimated cost, and estimated rows. This tells you exactly which table and which step is the bottleneck.

Step 5: After fixing the query (e.g. adding an index), click ⚡🔍 again and compare. The red box should turn blue.

Tip: If you’ve already run the query, click the “Execution Plan” tab at the bottom of the results panel to see the same diagram.

---

Quick Workflow — How to Investigate a Slow Query

1. Run EXPLAIN on the query
2. Find any row where type = ALL
3. Check if key = NULL on that same row
4. If yes → no index on this table for this query
5. Add an index on the column used in WHERE or JOIN
6. Run EXPLAIN again — confirm type changed to ref or range

Most slow queries are fixed by steps 4–5. The rest of the time it’s a query rewrite problem — but start here.

---

The Extra Column — Two Things to Watch For

The Extra column appears in EXPLAIN output and can reveal hidden expensive steps. Two values to know:

Using filesort — MySQL needs to sort your results (for ORDER BY) but there’s no index it can use to get them pre-sorted. So it reads all the matching rows first, then sorts them separately as a second step. On large result sets this is slow.

Fix: add an index on the column in your ORDER BY clause.

Using temporary — MySQL had to create a temporary table in memory to process your query. This happens with complex GROUP BY or ORDER BY queries. It means MySQL couldn’t process the result in one pass — it needed scratch space. The bigger the result set, the slower this gets.

Fix: add an index on the GROUP BY or ORDER BY column, or simplify the query.

Neither of these will crash anything. But if a query is slow and you see one of these in Extra, that’s a strong clue about why.

---

Try This

Run EXPLAIN on each query and for each one answer: what is type, what is key, and is there a problem?

-- Query 1
EXPLAIN
SELECT * FROM employees WHERE employee_id = 5;

-- Query 2
EXPLAIN
SELECT * FROM employees WHERE salary > 80000;

-- Query 3
EXPLAIN
SELECT e.first_name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id;

What to expect

**Query 1:** `type = const`, `key = PRIMARY`. MySQL is using the primary key index to find exactly one row — as fast as it gets. **Query 2:** `type = ALL`, `key = NULL` (unless you've added an index on salary). MySQL scans every employee to find those earning over 80,000. On a small training table this is instant. On a payroll table with 200,000 rows it would be slow. **Query 3:** Two rows in the output — one for `employees`, one for `departments`. MySQL processes one table first then uses that result to look up matching rows in the second. Check `type` and `key` for each. The `department_id` column (used in the JOIN) should already have a foreign key index, so you should see `key` populated for at least one table.

---

Quick Recap

• An index is a sorted lookup structure — lets MySQL jump to matching rows instead of reading every row
• EXPLAIN shows what MySQL plans to do before it runs the query
• type = ALL means no index — full table scan — slow on large tables
• key = NULL means no index was used — adding one is usually the fix
• rows is how many rows MySQL estimates scanning — lower is better
• In Workbench: click ⚡🔍 for Visual EXPLAIN — red boxes are the expensive parts, click them for details
• Using filesort = separate sort step (add index on ORDER BY column)
• Using temporary = temp table created (add index on GROUP BY column or simplify)

---

Up Next

You’ve completed the full Part 3 syllabus!

Final Challenge → Mini Challenge 11

Or go back to the Course Index.