Indexes in SQL

Problem

The database reads everything

The rows in the table are not sorted by customer. To find the 60 visits of customer 1234, the database reads all 300,000 rows and checks each one.

Imagine looking for the word “index” in a textbook with no index at the back. You would have to turn every page. That is why a book has an alphabetical index at the end: the words are sorted, and next to each one are the pages where it appears.

An index on a table is the same thing. It is a separate, sorted copy of one column, where each value has a note saying where its row is. Because it is sorted, the database searches by halving: it looks in the middle, works out which half 1234 is in, then looks in the middle of that half, and so on. For 300,000 values about 19 steps are enough.

Here it is on a small table with six visits, first without an index, then with one:

SELECT * FROM visits WHERE customer_id = 3;
CREATE INDEX idx_visits_customer ON visits(customer_id);
SELECT * FROM visits WHERE customer_id = 3;

Step by step, in words

6 visits. The rows are not sorted by customer_id: 5, 3, 8, 3, 1, 6.
Without an index the database does a SCAN: it reads row by row and compares customer_id with 3.
6 rows of 6 were read, and 2 match: the rows with id 2 and 4.
CREATE INDEX makes a sorted copy of customer_id: 1, 3, 3, 5, 6, 8. Next to each value it notes which row it is.
With the index it is a SEARCH. In the middle is 5, more than 3: so 3 is in the half with the smaller values, above 5.
In the middle of that half is 3. Above it is 1, which is less: this is the first 3.
Next to 3 is row 2, next to the next 3 row 4. Then 5: done. Only 2 rows are read from the table.

You create an index with CREATE INDEX name ON table(column). Run the field twice and compare the times:

SQLdatabase: shop

CREATE INDEX IF NOT EXISTS idx_visits_customer ON visits(customer_id);
SELECT COUNT(*) FROM visits WHERE customer_id = 1234;

The first run builds the index, so it is slow. On the second run the index is already there (IF NOT EXISTS skips creating it, as with CREATE TABLE), and only the search is left. Compare it with the time of the query without an index in the previous card: the difference is hundreds of times.

More precisely, if you are curious

Indexes in SQLite, PostgreSQL, MySQL and SQL Server are B-trees by default. That is not a list that gets halved but a tree of pages: each page holds hundreds of sorted values and points to the next level. So even for millions of rows three or four page reads are enough. The idea is the same: sorted, and each step throws away almost everything that is not needed.

Try it

How often a product is viewed

Each product page will show how many times it has been viewed. Create an index that speeds up searching by product, then count the visits of product 7.

SQLdatabase: shop

SELECT COUNT(*) FROM visits WHERE product_id = 7;

Solution

CREATE INDEX idx_visits_product ON visits(product_id);
SELECT COUNT(*) FROM visits WHERE product_id = 7;

The idea

Indexes you already have

Searching by primary key is fast without CREATE INDEX:

SQLdatabase: shop

SELECT * FROM visits WHERE id = 150000;

In SQLite an INTEGER PRIMARY KEY column is the order of the table itself: the rows are stored by id. So no separate index is needed.

For UNIQUE the database makes an index on its own. Without it, every new email would mean reading all customers to check whether it is already there. SQLite keeps the description of the database in the sqlite_master table. See which indexes the shop has:

SQLdatabase: shop

SELECT name, tbl_name FROM sqlite_master WHERE type = 'index';

The first one is for email in customers. The second is for the primary key of order_items, which has two columns (order_id, product_id) and is not an INTEGER PRIMARY KEY.

See it

DROP INDEX

You remove an index with DROP INDEX name. The data does not change: only the index and the fast search go away. In this field the index on the customer has already been created. Run the query and look at the time:

SQLdatabase: shop

DROP INDEX idx_visits_customer;
SELECT COUNT(*) FROM visits WHERE customer_id = 1234;

The time is back to what it was without an index. If you run the field a second time, SQLite will say there is no such index. The “Fresh database” button brings back the starting state.

Exercise

Two tasks

1. The “visits per day” report runs every morning. Create an index that speeds it up, then use it to count the visits on 1 March 2026.

An index for searching by date, and the number of visits on 2026-03-01:

SQLdatabase: shop

SELECT COUNT(*) FROM visits WHERE seen_at = '2026-03-01';

Solution

CREATE INDEX idx_visits_seen ON visits(seen_at);
SELECT COUNT(*) FROM visits WHERE seen_at = '2026-03-01';

2. The page with the view count of a product has been switched off. The database has two indexes: idx_visits_customer and idx_visits_product. Remove the one that is no longer needed.

Remove the index nobody uses:

SQLdatabase: shop

SELECT name FROM sqlite_master WHERE type = 'index';

Solution

DROP INDEX idx_visits_product;

Test

Check yourself

1. The table has no index on customer_id. What does the database do for WHERE customer_id = 1234?It goes straight to the right rowsIt reads every row and checks each oneIt returns an error

2. Why is searching an index fast?Because the values are sorted and each step halves what is leftBecause it is smaller than the tableBecause it is in memory

3. The customers table has email TEXT NOT NULL UNIQUE and id INTEGER PRIMARY KEY. Which search is fast without a new index?Only by idBy id and by emailOnly by email

4. What happens to the data when you run DROP INDEX?The table is emptiedThe rows the index points to are deletedNothing: only the index and the fast search go away

Indexes

Cookies