Full table scan versus index lookup
40 min · Intermediate
Need help? Ask a tutor on WhatsApp (opens in a new tab)To answer a query, MySQL must find the matching rows. Without help it reads every row of the table; with an index it can jump almost straight to the right ones. That one difference is why the same query takes 2 milliseconds on one table and 2 seconds on another.
Full table scan: how MySQL finds rows without an index
A full table scan means MySQL reads every row of a table, one after another, and checks each row against the WHERE condition. It happens when nothing tells MySQL where the matching rows are.
Without an index MySQL has to look at every row. The script below counts how many rows MySQL examines to answer a query, and compares that with the rows it returns.
Example 1 · Count the rows MySQL examines
SELECT customer_id, nameFROM customersWHERE city = 'Pune'ORDER BY customer_id;
SELECT ROWS_EXAMINED AS rows_examined, ROWS_SENT AS rows_returnedFROM performance_schema.events_statements_historyWHERE THREAD_ID = PS_CURRENT_THREAD_ID()ORDER BY EVENT_ID DESCLIMIT 1;+-------------+--------------+ | customer_id | name | +-------------+--------------+ | 5 | Priya Sharma | | 9 | Sneha Patil | +-------------+--------------+ +---------------+---------------+ | rows_examined | rows_returned | +---------------+---------------+ | 10 | 2 | +---------------+---------------+
What this code does
- The first query returns 2 customers, but
rows_examinedis 10: there is no index oncity, so MySQL read all ten customers and kept the two from Pune. - The helper query reads
events_statements_history, where MySQL keeps the last few statements of each connection.PS_CURRENT_THREAD_ID()(MySQL 8.0.16 and later) means "this connection", andORDER BY EVENT_ID DESC LIMIT 1picks the statement just before the helper. - Keep this helper handy: the rest of the module uses it to prove whether an index helped.
Why must it read everything? Rows are not stored in any order that helps your question. To find the customers from Pune, MySQL cannot know which rows hold 'Pune' until it has looked at all of them. A row it skipped might have been a match. On the 10 customers of the practice database this costs nothing. On 10 crore railway bookings it means reading 10 crore rows to return one, and that is the most common reason a page in an app feels slow.
- Rows examined: how many rows MySQL looked at to answer the query.
- Rows returned: how many rows came back in the result.
- A healthy lookup examines about as many rows as it returns. A full scan examines the whole table.
- When MySQL must sort the result itself, the sorted rows are counted again, so the examples here sort by the primary key or return a single row.
Predict the output
How many rows are examined?
Use the practice database. Only primary keys and unique emails are indexed.
Round 1 of 3
customers has no index on city. How many rows does MySQL examine?
performance_schema, a built-in MySQL database that records what each connection did. Run it in the mysql client right after your query. MySQL Workbench sends extra statements of its own, but it shows the same number in the "Query Stats" tab under the result.An index: a sorted lookup structure
An index is a separate structure that keeps the values of one or more columns in sorted order, and every entry points to the row it came from. MySQL jumps to the right entries instead of reading the whole table.
An index lets MySQL jump to the matching entries instead of reading every row. The picture compares the two ways; the script below creates an index and shows the rows examined fall.
Example 2 · Create an index and examine fewer rows
CREATE INDEX idx_customers_city ON customers (city);
-- A picture of what the index holds: every city in sorted order,-- each with the primary key of its row.SELECT city, customer_idFROM customersORDER BY city, customer_id;
SELECT customer_id, nameFROM customersWHERE city = 'Pune'ORDER BY customer_id;
SELECT ROWS_EXAMINED AS rows_examined, ROWS_SENT AS rows_returnedFROM performance_schema.events_statements_historyWHERE THREAD_ID = PS_CURRENT_THREAD_ID()ORDER BY EVENT_ID DESCLIMIT 1;+-----------+-------------+ | city | customer_id | +-----------+-------------+ | Bengaluru | 1 | | Chennai | 2 | | Chennai | 3 | | Delhi | 4 | | Delhi | 8 | | Hyderabad | 10 | | Kochi | 7 | | Mumbai | 6 | | Pune | 5 | | Pune | 9 | +-----------+-------------+ +-------------+--------------+ | customer_id | name | +-------------+--------------+ | 5 | Priya Sharma | | 9 | Sneha Patil | +-------------+--------------+ +---------------+---------------+ | rows_examined | rows_returned | +---------------+---------------+ | 2 | 2 | +---------------+---------------+
What this code does
- The middle query shows the idea of the index: cities sorted A to Z, each next to the
customer_idof its row. Finding'Pune'in a sorted list is like finding a name in your phone's contact list. - After the index,
rows_examineddrops from 10 to 2. MySQL jumped to the first'Pune'entry, read the two matches and stopped at'Pune''s end. - The result is exactly the same as before. An index changes how fast rows are found, never which rows are returned.
An index is like the index at the back of a textbook: you find "Normalisation" in the sorted list and turn straight to page 212, instead of reading the whole book.
Because the entries are sorted, MySQL does not read them all. It jumps to the first 'Pune' entry, reads entries while they still say 'Pune', and stops. Every row it touches is a match. An index is not free. It takes disk space, and MySQL must update it on every INSERT, UPDATE and DELETE. The lesson "The cost of indexes on writes" weighs that cost; for now, see what an index buys you.
CREATE INDEX name ON table (column) builds an index. The lesson "Creating indexes" covers every form of it; one line is enough here.Making a big table with a recursive CTE
A recursive CTE is a WITH query that refers to itself: an anchor part starts with one row, and the recursive part keeps adding the next row until a condition stops it.
With 10 rows every query is instant, so an index shows no gain. The script below uses a recursive CTE to generate 100,000 train bookings, enough to make a full scan visible.
Example 3 · Generate 100,000 train bookings
SET SESSION cte_max_recursion_depth = 100000;
CREATE TABLE train_bookings ( booking_id INT PRIMARY KEY, pnr CHAR(10) NOT NULL, train_no INT NOT NULL, travel_date DATE NOT NULL, fare DECIMAL(8,2) NOT NULL);
INSERT INTO train_bookings (booking_id, pnr, train_no, travel_date, fare)WITH RECURSIVE seq AS ( SELECT 1 AS n UNION ALL SELECT n + 1 FROM seq WHERE n < 100000)SELECT n, CAST(4210000000 + n * 7919 AS CHAR), 12000 + n % 500, DATE '2026-01-01' + INTERVAL (n % 365) DAY, 200 + (n * 37) % 2800FROM seq;
SELECT COUNT(*) AS bookings, COUNT(DISTINCT train_no) AS trains, MIN(travel_date) AS first_day, MAX(travel_date) AS last_dayFROM train_bookings;
SELECT *FROM train_bookingsWHERE booking_id <= 3ORDER BY booking_id;+----------+--------+------------+------------+ | bookings | trains | first_day | last_day | +----------+--------+------------+------------+ | 100000 | 500 | 2026-01-01 | 2026-12-31 | +----------+--------+------------+------------+ +------------+------------+----------+-------------+--------+ | booking_id | pnr | train_no | travel_date | fare | +------------+------------+----------+-------------+--------+ | 1 | 4210007919 | 12001 | 2026-01-02 | 237.00 | | 2 | 4210015838 | 12002 | 2026-01-03 | 274.00 | | 3 | 4210023757 | 12003 | 2026-01-04 | 311.00 | +------------+------------+----------+-------------+--------+
What this code does
SET SESSION cte_max_recursion_depth = 100000lets this connection's CTE run 100,000 rounds.seqstarts at 1 (SELECT 1 AS n) and addsn + 1untiln < 100000is false.- Each number becomes a row:
4210000000 + n * 7919gives a different 10-digit PNR for every booking,n % 500spreads the bookings over 500 trains,n % 365over every day of 2026, and(n * 37) % 2800gives fares from Rs. 200 to Rs. 2,999. INSERT INTO ... WITH RECURSIVE ... SELECTfills the table in one statement. It takes a few seconds; the rows are made up, but the table is big enough to show a full scan.
Example 4 · Mistake: forgetting the recursion limit
SELECT @@cte_max_recursion_depth;
WITH RECURSIVE seq AS ( SELECT 1 AS n UNION ALL SELECT n + 1 FROM seq WHERE n < 5000)SELECT COUNT(*) FROM seq;+---------------------------+ | @@cte_max_recursion_depth | +---------------------------+ | 1000 | +---------------------------+ ERROR 3636 (HY000): Recursive query aborted after 1001 iterations. Try increasing @@cte_max_recursion_depth to a larger value.
What this code does
@@cte_max_recursion_depthis 1000 by default, so a CTE that needs 5,000 rounds is stopped after 1,001 and nothing is returned.- The fix is the line from Example 3, before the query:
SET SESSION cte_max_recursion_depth = 5000;.SESSIONchanges it only for your connection, so other users keep the safe default.
Why build a big table? Real tables at IRCTC, a bank or an online shop hold crores of rows, and a few lakh generated rows are enough to make a full scan visible on your own computer.
MySQL stops a recursive CTE after 1,000 rounds by default, to protect you from a query that never ends. For more rows, raise the session variable cte_max_recursion_depth first.
Timing a query before and after an index
The buffer pool is MySQL's memory cache of table and index data. A query run a second time is often faster because its pages are already in the buffer pool, so time alone can mislead.
Timing a query means measuring how long it takes; the mysql client prints it after every result. The scripts below time a PNR lookup before and after an index, then prove the gain with rows examined.
Example 5 · Time a PNR lookup before and after an index
SET SESSION cte_max_recursion_depth = 100000;
CREATE TABLE train_bookings ( booking_id INT PRIMARY KEY, pnr CHAR(10) NOT NULL, train_no INT NOT NULL, travel_date DATE NOT NULL, fare DECIMAL(8,2) NOT NULL);
INSERT INTO train_bookings (booking_id, pnr, train_no, travel_date, fare)WITH RECURSIVE seq AS ( SELECT 1 AS n UNION ALL SELECT n + 1 FROM seq WHERE n < 100000)SELECT n, CAST(4210000000 + n * 7919 AS CHAR), 12000 + n % 500, DATE '2026-01-01' + INTERVAL (n % 365) DAY, 200 + (n * 37) % 2800FROM seq;
SELECT * FROM train_bookings WHERE pnr = '4211583800';
CREATE INDEX idx_bookings_pnr ON train_bookings (pnr);
SELECT * FROM train_bookings WHERE pnr = '4211583800';+------------+------------+----------+-------------+---------+ | booking_id | pnr | train_no | travel_date | fare | +------------+------------+----------+-------------+---------+ | 200 | 4211583800 | 12200 | 2026-07-20 | 2000.00 | +------------+------------+----------+-------------+---------+ 1 row in set (0.944 sec) +------------+------------+----------+-------------+---------+ | booking_id | pnr | train_no | travel_date | fare | +------------+------------+----------+-------------+---------+ | 200 | 4211583800 | 12200 | 2026-07-20 | 2000.00 | +------------+------------+----------+-------------+---------+ 1 row in set (0.030 sec)
What this code does
- This is a real run in the mysql client; your times will differ with your computer and MySQL version, and some versions print two decimal places instead of three.
- Before the index, MySQL scanned all 100,000 bookings to find one PNR. After
CREATE INDEX, the same query jumps to the PNR in the sorted index, so it takes a tiny fraction of the time. - The
CREATE INDEXitself took a couple of seconds: building an index means sorting every PNR once. You pay that once; every later lookup is fast.
Example 6 · Prove it with rows examined
SET SESSION cte_max_recursion_depth = 100000;
CREATE TABLE train_bookings ( booking_id INT PRIMARY KEY, pnr CHAR(10) NOT NULL, train_no INT NOT NULL, travel_date DATE NOT NULL, fare DECIMAL(8,2) NOT NULL);
INSERT INTO train_bookings (booking_id, pnr, train_no, travel_date, fare)WITH RECURSIVE seq AS ( SELECT 1 AS n UNION ALL SELECT n + 1 FROM seq WHERE n < 100000)SELECT n, CAST(4210000000 + n * 7919 AS CHAR), 12000 + n % 500, DATE '2026-01-01' + INTERVAL (n % 365) DAY, 200 + (n * 37) % 2800FROM seq;
SELECT booking_id, train_no, travel_date, fareFROM train_bookingsWHERE pnr = '4211583800';
SELECT ROWS_EXAMINED AS rows_examined, ROWS_SENT AS rows_returnedFROM performance_schema.events_statements_historyWHERE THREAD_ID = PS_CURRENT_THREAD_ID()ORDER BY EVENT_ID DESCLIMIT 1;
CREATE INDEX idx_bookings_pnr ON train_bookings (pnr);
SELECT booking_id, train_no, travel_date, fareFROM train_bookingsWHERE pnr = '4211583800';
SELECT ROWS_EXAMINED AS rows_examined, ROWS_SENT AS rows_returnedFROM performance_schema.events_statements_historyWHERE THREAD_ID = PS_CURRENT_THREAD_ID()ORDER BY EVENT_ID DESCLIMIT 1;+------------+----------+-------------+---------+ | booking_id | train_no | travel_date | fare | +------------+----------+-------------+---------+ | 200 | 12200 | 2026-07-20 | 2000.00 | +------------+----------+-------------+---------+ +---------------+---------------+ | rows_examined | rows_returned | +---------------+---------------+ | 100000 | 1 | +---------------+---------------+ +------------+----------+-------------+---------+ | booking_id | train_no | travel_date | fare | +------------+----------+-------------+---------+ | 200 | 12200 | 2026-07-20 | 2000.00 | +------------+----------+-------------+---------+ +---------------+---------------+ | rows_examined | rows_returned | +---------------+---------------+ | 1 | 1 | +---------------+---------------+
What this code does
- Same query, same one-row result. Before the index MySQL examined 100,000 rows; after it, 1 row.
- Unlike time, these numbers are the same on every computer and every run, so they make the best evidence in a report or an interview answer.
Time is useful but noisy. It depends on your computer, on what else is running, and on whether the data is already in the buffer pool. So measure rows examined as well. That number is the same on every computer, and it shows why the time changed.
The rule to remember: a full scan grows with the table, so it is O(n). An index lookup reads a few levels of a sorted tree plus the matching rows, about O(log n). At 100 rows you cannot tell them apart; at 1 crore rows one takes seconds and the other milliseconds.
EXPLAIN, which the lesson "Reading EXPLAIN" covers.Next step
Check your understanding
12 quick questions