SQL Indexes Explained: Make Queries Fast (and When Not To)

On a table with five rows, every query is instant. On a table with fifty million rows, a query that has to read every row can take minutes. An index lets the database jump straight to the rows it needs, the same way a book's index takes you to the right page without reading the whole book.

How an index works#

Without an index, finding WHERE email = 'ravi@company.com' means checking every row: a full table scan. An index is a separate, sorted structure (usually a B-tree) that stores the column's values together with pointers to their rows. Because it is sorted, the database can find a value in a handful of steps, even among millions.

Creating an index#

CREATE INDEX idx_employees_email
ON employees (email);

The name (idx_employees_email) is up to you; a idx_table_column convention keeps them easy to find. Once created, the database decides automatically when to use the index. Your queries do not change.

Seeing the difference with EXPLAIN#

EXPLAIN shows how the database plans to run a query. In SQLite, EXPLAIN QUERY PLAN:

EXPLAIN QUERY PLAN
SELECT * FROM employees WHERE email = 'ravi@company.com';

Before the index:

SCAN employees

After CREATE INDEX idx_employees_email ON employees (email):

SEARCH employees USING INDEX idx_employees_email (email=?)

SCAN means every row is read; SEARCH ... USING INDEX means the index is used. PostgreSQL and MySQL use EXPLAIN (and EXPLAIN ANALYZE to actually run and time the query); look for "Seq Scan" versus "Index Scan".

Unique indexes#

A unique index also enforces that no two rows share a value:

CREATE UNIQUE INDEX idx_employees_email
ON employees (email);

INSERT INTO employees (id, name, email)
VALUES (6, 'Copy', 'ravi@company.com');

ERROR: UNIQUE constraint failed: employees.email

Primary keys and UNIQUE constraints create unique indexes automatically, which is why lookups by id are always fast.

Composite (multi-column) indexes#

An index can cover several columns. It is sorted by the first column, then by the second within it, like a phone book sorted by surname, then first name:

CREATE INDEX idx_orders_customer_date
ON orders (customer_id, order_date);

It helps queries that filter on the leftmost columns:

EXPLAIN QUERY PLAN
SELECT * FROM orders
WHERE customer_id = 1 AND order_date >= '2026-02-01';

SEARCH orders USING INDEX idx_orders_customer_date (customer_id=? AND order_date>?)

EXPLAIN QUERY PLAN
SELECT * FROM orders
WHERE order_date >= '2026-02-01';

SCAN orders

A phone book sorted by surname cannot help you find everyone called "Ravi". Put the column you filter by most (with =) first.

Functions can stop an index being used#

-- Index on email exists, but this cannot use it
EXPLAIN QUERY PLAN
SELECT * FROM employees WHERE LOWER(email) = 'ravi@company.com';

SCAN employees

The index stores email, not LOWER(email). Either store values in a consistent case, or create an expression index where supported: CREATE INDEX idx_email_lower ON employees (LOWER(email)); (PostgreSQL, SQLite, MySQL 8.0.13+). The same applies to dates: write order_date >= '2026-01-01' AND order_date < '2027-01-01' instead of YEAR(order_date) = 2026.

The cost of indexes#

Indexes are not free:

• Every INSERT, UPDATE and DELETE must also update every index on the table, so writes get slower.
• Each index uses disk space and memory.
• The optimiser may ignore an index anyway if a query matches a large share of the rows.

What to index#

Good candidates:

• Columns used in WHERE filters on large tables, especially with =.
• Foreign key columns used in joins (orders.customer_id). PostgreSQL does not index them automatically.
• Columns used in ORDER BY with LIMIT (a sorted index avoids sorting).

Usually not worth it:

• Small tables (a scan is already fast).
• Columns with very few distinct values, such as a yes/no flag, on their own.
• Tables that are written far more often than they are read.

Removing an index: DROP INDEX idx_employees_email; (MySQL: DROP INDEX idx_employees_email ON employees;).

Common mistakes#

Practice#

SELECT order_id, amount
FROM orders
WHERE customer_id = ?
ORDER BY order_date DESC
LIMIT 10;

CREATE INDEX idx_orders_customer_date
ON orders (customer_id, order_date);

Key takeaway#