How SQL Indexes Work and How to Tell If a Query Needs One

Your query works, but it's slow, and it was fast when the table was small. That's the classic sign you need an index.

Think of the index at the back of a book. If you want every page that mentions "photosynthesis," you don't read the entire book. You look it up in the index and jump to the pages. A database index does the same for rows.

What happens without an index

When you filter on a column that has no index, the database reads every row and checks each one. This is called a full table scan. It's fine for 500 rows and painful for 50 million.

Let's create a table with enough rows to be realistic and look at how the database plans to run a query. Run this in the SQL editor.

CREATE TABLE users (id INTEGER PRIMARY KEY, email TEXT, city TEXT, created_at TEXT);

WITH RECURSIVE seq(n) AS (
  SELECT 1 UNION ALL SELECT n + 1 FROM seq WHERE n < 5000
)
INSERT INTO users (email, city, created_at)
SELECT 'user' || n || '@example.com',
       CASE n % 4 WHEN 0 THEN 'Toronto' WHEN 1 THEN 'Ottawa' WHEN 2 THEN 'Calgary' ELSE 'Halifax' END,
       date('2024-01-01', '+' || (n % 365) || ' days')
FROM seq;

EXPLAIN QUERY PLAN
SELECT * FROM users WHERE email = 'user4321@example.com';

SCAN users

The plan says SCAN. The database will walk through all 5,000 rows to find that one email. (EXPLAIN is available in most databases. The wording of the plan differs, but it's the same idea.)

Add an index

CREATE INDEX idx_users_email ON users (email);

EXPLAIN QUERY PLAN
SELECT * FROM users WHERE email = 'user4321@example.com';

SEARCH users USING INDEX idx_users_email (email=?)

Now it says SEARCH using the index. Instead of checking thousands of rows, the database jumps straight to the right one. An index is typically stored as a sorted tree, so finding a value takes a handful of steps even with millions of rows.

Composite indexes and column order

An index can cover several columns. The order matters a lot. Suppose you often run this query:

EXPLAIN QUERY PLAN
SELECT * FROM users WHERE city = 'Toronto' AND created_at >= '2024-06-01';

SCAN users

It's a scan again. Add an index on both columns, with the equality column first:

CREATE INDEX idx_users_city_created ON users (city, created_at);

EXPLAIN QUERY PLAN
SELECT * FROM users WHERE city = 'Toronto' AND created_at >= '2024-06-01';

SEARCH users USING INDEX idx_users_city_created (city=? AND created_at>?)

Think of a phone book sorted by last name, then first name. You can find "Smith, Anna" quickly, and "all the Smiths." But you can't use it to find everyone named "Anna" without checking every page.

The same idea applies here. An index on (city, created_at) helps queries that filter on city, or on city and created_at. It doesn't help a query that filters on created_at alone. A good rule: put the columns you compare with = first, then the ones you compare with ranges (>, <, BETWEEN).

EXPLAIN QUERY PLAN
SELECT * FROM users WHERE created_at >= '2024-06-01';

SCAN users

Things that quietly stop an index from working

Having an index doesn't guarantee it gets used. These patterns are common culprits:

Wrapping the column in a function. The database can't use the index on email if you search on lower(email).

EXPLAIN QUERY PLAN
SELECT * FROM users WHERE lower(email) = 'user4321@example.com';

SCAN users

Fix it by storing the data in a consistent form, or by creating an index on the expression itself (CREATE INDEX ... ON users (lower(email))), which SQLite and PostgreSQL both support.

A leading wildcard. LIKE 'user43%' can use an index on many databases. LIKE '%4321@example.com' cannot, because the start of the value is unknown.

Doing math on the column. WHERE age + 1 = 30 can't use an index on age. Write WHERE age = 29 instead.

Mismatched types. Comparing a number column to a string value can force a conversion on every row.

Low selectivity. An index on a column with only a few distinct values, such as a true/false flag, rarely helps because it doesn't narrow things down much.

Indexes aren't free

Every index has a cost:

• It takes up disk space.
• Every INSERT, UPDATE, and DELETE has to update each index on the table, so writes get slower.

That means "index everything" is a bad plan. Add indexes for the queries you actually run, and drop ones nobody uses.

Indexes you already have

Primary keys and UNIQUE constraints create indexes automatically. That's one of the reasons they're also fast to look up. If a column is a foreign key you join on, such as orders.customer_id, it usually deserves an index too, since joins constantly look rows up by it.

A simple routine for tuning a slow query

1. Find the slow query. Don't guess.
2. Run EXPLAIN (or EXPLAIN QUERY PLAN in SQLite, EXPLAIN ANALYZE in PostgreSQL).
3. Look for full scans on large tables.
4. Add an index that matches the WHERE, JOIN, and ORDER BY columns.
5. Run EXPLAIN again to confirm it's being used, and time the query.

Key takeaways

• An index lets the database jump to rows instead of scanning the whole table.
• Use EXPLAIN to see whether an index is being used.
• In composite indexes, column order matters. Equality columns first, ranges after.
• Functions, leading wildcards, and math on the column can break index use.
• Each index slows down writes, so add only what your queries need.