Indexes & EXPLAIN

With twelve customers, every query in this course finishes instantly. With twelve lakh customers, a query that searches by city could take seconds, and a busy website runs that query hundreds of times a minute. The difference between a slow database and a fast one is usually not the server. It is indexes.

In this lesson you will learn what an index is, how MySQL uses it, how to order the columns in an index that covers several columns, how to read EXPLAIN to see whether your index is used, and when you should not add one.

What an index is

Think of the index at the back of a textbook. To find every page about "photosynthesis", you do not read all 400 pages. You look up the word in the alphabetical index and jump to the listed pages.

Without an index, MySQL must do the 400-page reading: a full table scan, checking every row to see if it matches. An index is a separate, sorted structure that holds the values of one or more columns plus a pointer to each row. Because it is sorted, MySQL can find a value quickly instead of checking everything.

MySQL's InnoDB engine stores indexes as B-trees (balanced trees). The top of the tree points to ranges of values, the next level to smaller ranges, and the bottom level holds the sorted values themselves. Even with millions of rows, the tree is only three or four levels deep, so finding one value takes a handful of steps instead of millions.

Two facts about InnoDB are useful to remember:

• The primary key is the table. InnoDB stores rows in primary key order, so looking up a row by its primary key is the fastest search there is.
• Other indexes, called secondary indexes, store the indexed column plus the primary key, and use that key to fetch the rest of the row.

Creating an index

You create an index with CREATE INDEX, giving it a name, the table, and the column:

CREATE INDEX idx_customers_city ON customers (city);

SELECT customer_id, first_name, last_name
FROM customers
WHERE city = 'Rohtak'
ORDER BY customer_id;

customer_id | first_name | last_name
------------+------------+----------
1           | Aarav      | Sharma
7           | Vikram     | Yadav
(2 rows)

The result is the same as without the index; indexes never change what a query returns, only how fast it runs. A clear naming habit is idx_ plus the table and column names.

Some indexes already exist without you asking. Every PRIMARY KEY and UNIQUE constraint is backed by an index, and InnoDB creates an index on every foreign key column. A unique index both speeds up searches and blocks duplicates:

CREATE UNIQUE INDEX uq_customers_email ON customers (email);

SELECT COUNT(*) AS customers, COUNT(email) AS with_email
FROM customers;

customers | with_email
----------+-----------
12        | 10
(1 row)

The index was created even though two customers have no email, because a unique index allows many NULL values.

Composite indexes and column order

An index can cover several columns. This is a composite index, and the order of its columns matters a lot. Think of a phone directory sorted by surname and then first name. It is perfect for finding "Sharma, Aarav" and good for finding every "Sharma", but useless for finding everyone named "Aarav", because the Aaravs are scattered across the whole book.

MySQL follows the same leftmost prefix rule. An index on (status, order_date) can help queries that filter on:

• status alone,
• status and order_date together,

but not order_date alone.

CREATE INDEX idx_orders_status_date ON orders (status, order_date);

SELECT order_id, customer_id, order_date
FROM orders
WHERE status = 'delivered'
  AND order_date >= '2025-04-01'
ORDER BY order_date;

order_id | customer_id | order_date
---------+-------------+-----------
110      | 8           | 2025-04-01
112      | 10          | 2025-04-18
114      | 11          | 2025-05-11
(3 rows)

This query uses the index fully: it jumps to the delivered entries, which are already sorted by date, and reads from 1 April onwards. A simple rule for choosing the order: columns compared with = first, the column used for a range (>, <, BETWEEN) last.

Reading EXPLAIN

How do you know whether MySQL uses your index? Put EXPLAIN in front of a SELECT. MySQL does not run the query; it shows its plan. EXPLAIN is MySQL-only, so it does not run in the browser.

EXPLAIN SELECT customer_id, first_name
FROM customers
WHERE city = 'Rohtak';

The result has one row per table in the query. These columns matter most:

Before idx_customers_city exists, a plan for this query on a large table shows type: ALL and key: NULL. After you create the index, you should see type: ref and key: idx_customers_city, and rows drops from the size of the table to roughly the number of matching customers. On a tiny table like the practice one, MySQL may still choose a full scan, because reading twelve rows directly is cheaper than using an index; test with realistic amounts of data.

EXPLAIN ANALYZE (MySQL 8.0.18 and later) goes further: it really runs the query and reports the actual time and row counts for each step.

Queries that cannot use an index

An index stores the column's raw values in order. If your query changes the column before comparing it, the sorted order is no use:

-- Cannot use an index on order_date: the function hides the column
SELECT order_id FROM orders WHERE YEAR(order_date) = 2025;

-- Can use it: a plain range on the raw column
SELECT order_id FROM orders
WHERE order_date >= '2025-01-01' AND order_date < '2026-01-01';

The second form asks the same question in a way the index understands. The same idea works for a single month, and it runs in the browser too:

SELECT order_id, order_date
FROM orders
WHERE order_date >= '2025-02-01'
  AND order_date < '2025-03-01'
ORDER BY order_date;

order_id | order_date
---------+-----------
104      | 2025-02-02
105      | 2025-02-14
106      | 2025-02-28
(3 rows)

Other common index killers: a LIKE pattern that starts with a wildcard (LIKE '%sharma'), and comparing a text column with a number (WHERE phone = 9812045678 instead of '9812045678'), which forces MySQL to convert every value.

When not to add an index

Indexes are not free. Every INSERT, UPDATE and DELETE must also update every index on the table, and each index uses disk space and memory. Avoid indexes:

• on small tables, where a full scan is already fast;
• on columns you rarely use in WHERE, JOIN or ORDER BY;
• on a column with very few different values, such as a true/false flag, used on its own;
• that duplicate another: an index on (status) is redundant when (status, order_date) exists, because of the leftmost prefix rule;
• on tables that are written far more than they are read, such as a raw log.

Add indexes for the queries your application actually runs, check them with EXPLAIN, and remove ones that are never used. SHOW INDEX FROM orders; lists a table's indexes, and DROP INDEX idx_orders_status_date ON orders; removes one.

Key points

• An index is a sorted structure (a B-tree in InnoDB) that lets MySQL find rows without scanning the whole table.
• Primary keys, UNIQUE constraints and foreign keys get indexes automatically.
• In a composite index, column order matters: put = columns first and the range column last.
• EXPLAIN shows the plan: type: ALL with key: NULL means a full table scan.
• Wrapping a column in a function, or a leading % in LIKE, stops MySQL from using its index.
• Every index slows down writes and uses space, so index only what your queries need.

CREATE INDEX idx_items_product_order ON order_items (product_id, order_id);

CREATE INDEX idx_products_category_price ON products (category, price);