Indexes: How They Work and When to Use Them
Speed up reads by letting the database jump straight to the rows it needs
INSERT, UPDATE and DELETE has a little more work to do and the index takes disk space. Knowing when that trade pays off is the whole skill.
- explain why a query without an index does a full table scan,
- describe how a B-tree index narrows a search to a handful of reads,
- name what an index speeds up (equality, ranges,
ORDER BY, joins) and what it costs, - choose good columns using selectivity and the leftmost-prefix rule for composite indexes,
- verify that an index is actually used with
EXPLAIN, - recognise the cases where you should not add an index.
1. The problem: a full table scan
Every example on this page uses one small table from an online shop. Create it once and load a few rows:
CREATE TABLE customers (
customer_id INT PRIMARY KEY,
email VARCHAR(120),
city VARCHAR(50),
created_at DATE
);
INSERT INTO customers (customer_id, email, city, created_at) VALUES
(1, 'ana@shop.io', 'Bishkek', '2025-01-04'),
(2, 'omar@shop.io', 'Osh', '2025-01-09'),
(3, 'lena@shop.io', 'Bishkek', '2025-02-14'),
(4, 'raj@shop.io', 'Naryn', '2025-02-20'),
(5, 'mira@shop.io', 'Osh', '2025-03-02');Now imagine this table holds a million rows and you run a very ordinary lookup:
SELECT * FROM customers WHERE email = 'raj@shop.io';With no index on email, the database has no idea where that row lives. It must read every single row and compare its email one at a time. That is a full table scan: on a million rows it does roughly a million comparisons to return one result.
Analogy. Think of a 900-page textbook with no index at the back. To find every page that mentions "photosynthesis" you would flip through all 900 pages. The alphabetical index at the back changes that: you jump to the letter P, read one line, and it tells you the exact pages. A database index is that back-of-the-book index for your rows.