SQL Indexes Explained: How They Work and When to Use Them

A query that feels instant on 100 rows can crawl on 10 million. Most of the time the cure is not a bigger server. It is a SQL index on the right column.

This guide builds on our SQL tutorial for beginners. If you join tables a lot, read SQL joins explained too, because join columns are prime candidates for indexes.

What an SQL index is

Think of the index at the back of a book. Instead of reading every page to find “foreign key”, you look it up in the sorted list and jump to the page. A database index works the same way: a sorted structure, usually a B-tree, that stores column values and pointers to the rows.

Without an index the database does a full table scan and checks every row. With one, it does an index seek and goes straight to the matching rows.

CREATE INDEX idx_users_email ON users (email);
DROP INDEX idx_users_email;

Our 200,000-row test

We created a users table with 200,000 rows in SQLite 3.50 and looked up one email address. The EXPLAIN QUERY PLAN command shows how the database will run a query.

SELECT * FROM users WHERE email = 'user150000@example.com';

-- before: SCAN users                                       (about 8.16 ms)
CREATE INDEX idx_users_email ON users (email);
-- after:  SEARCH users USING INDEX idx_users_email (email=?)  (about 0.003 ms)

The word SCAN means every row was read. SEARCH ... USING INDEX means the database jumped to the match. Times are averages from our run; yours will differ, but the gap grows with table size. Other databases print plans with EXPLAIN or EXPLAIN ANALYZE.

Composite indexes and column order

When queries filter on several columns, one index on both can beat two separate ones.

SELECT COUNT(*) FROM users WHERE country = 'IN' AND age > 60;

CREATE INDEX idx_country_age ON users (country, age);
-- before: about 10.81 ms      after: about 0.35 ms
-- plan: SEARCH users USING COVERING INDEX idx_country_age (country=? AND age>?)

Order matters. This index works for filters on country, or on country and age. A query that filters only on age cannot seek into it. In our test it fell back to scanning the index, which is better than scanning the table but not a true lookup:

SELECT COUNT(*) FROM users WHERE age > 60;
-- plan: SCAN users USING COVERING INDEX idx_country_age

A good rule: put columns you compare with = first and a range column last.

What stops an index from being used

• A function on the column. WHERE LOWER(email) = ... cannot use a plain index on email. Our test plan showed SCAN users.
• A leading wildcard. WHERE email LIKE '%150000@example.com' forces a scan as well. LIKE 'user15%' can use an index.
• Low-selectivity columns. An index on a yes/no column helps little because half of the rows match.
• Type mismatches. Comparing a number column with a string can disable the index in some databases.

The cost of indexes

Every index must be updated on each insert, update and delete, and it takes disk space. We inserted 20,000 rows into the same table once with our two extra indexes and once without them. The version with indexes was about twice as slow, in a test that took only a few hundredths of a second, so the exact numbers are small, but the direction is always the same.

When to add an index

1. Find slow queries first, from logs or the query plan, not by guessing.
2. Index columns used in WHERE, JOIN and ORDER BY of frequent queries.
3. Prefer high-selectivity columns such as email or order number.
4. Check the plan again after adding the index to confirm it is used.
5. Drop indexes nobody uses. They still cost every write.

Testers can verify this too. Compare query times with and without data growth when you validate database behaviour, using the queries in our SQL interview questions for testers.

Final thoughts

Read the plan, add the smallest useful index and read the plan again. That loop will fix most slow queries without any other change.

Frequently asked questions

Keep learning