Indexes

Without help, finding rows means scanning the entire table — fine for twelve products, painful for twelve million. An index is a separate, sorted structure that lets the database jump straight to matching rows, like the index at the back of a book. Indexes are the single biggest tool for query performance.

Creating an index

CREATE INDEX idx_orders_customer_id ON orders (customer_id);

SELECT id, order_date FROM orders WHERE customer_id = 1;

The query looks exactly the same — indexes are invisible to SELECT. The database decides whether using the index is worthwhile.

Seeing whether an index is used

EXPLAIN QUERY PLAN
SELECT id, order_date FROM orders WHERE customer_id = 1;

CREATE INDEX idx_orders_customer_id ON orders (customer_id);

EXPLAIN QUERY PLAN
SELECT id, order_date FROM orders WHERE customer_id = 1;

The detail column is the part to read. SCAN means reading every row; SEARCH … USING INDEX means jumping straight to the matches. Other databases use EXPLAIN (PostgreSQL, MySQL) or graphical execution plans (SQL Server) — lesson 51 goes deeper.

What indexes speed up

• WHERE conditions using =, <, >, BETWEEN, IN and prefix LIKE 'abc%'
• Join conditions (index the foreign-key columns)
• ORDER BY on indexed columns, avoiding a sort
• MIN/MAX on an indexed column

Unique indexes

CREATE UNIQUE INDEX idx_employees_name ON employees (first_name, last_name);
INSERT INTO employees (first_name, last_name, email, job_title, salary, hire_date)
VALUES ('Mei', 'Tanaka', 'mei2@shop.example', 'Intern', 30000, '2026-03-01');

Error: UNIQUE constraint failed: employees.first_name, employees.last_name

A unique index both speeds up lookups and enforces uniqueness. Primary keys and UNIQUE constraints create one automatically.

Composite indexes and column order

CREATE INDEX idx_orders_status_date ON orders (status, order_date);

EXPLAIN QUERY PLAN
SELECT id FROM orders WHERE status = 'pending' AND order_date >= '2026-02-15';

A multi-column index is sorted by the first column, then the second within it — like a phone book sorted by surname, then first name. It helps queries that filter on the first column (or the first and second), but not on the second column alone. Put columns compared with = first and range conditions last.

Filtering on order_date alone can't jump into the index:

CREATE INDEX idx_orders_status_date ON orders (status, order_date);

EXPLAIN QUERY PLAN
SELECT id FROM orders WHERE order_date >= '2026-02-15';

It's still a SCAN. SQLite chose to read the whole index because it's smaller than the table and contains every column the query needs, but it can't jump to the matching dates.

Covering indexes

If an index contains every column a query needs, the database can answer from the index alone without touching the table — SQLite reports this as USING COVERING INDEX. PostgreSQL and SQL Server let you add extra non-key columns to an index with INCLUDE (…) for this purpose.

The cost of indexes

• Every INSERT, UPDATE and DELETE must update every index on the table.
• Indexes take disk space and memory.
• Small tables gain nothing — a scan is already instant.
• Indexes on columns with few distinct values (a yes/no flag) rarely help.

Index the columns your important queries filter, join and sort on — and confirm with the query plan.

Things that prevent index use

CREATE INDEX idx_customers_city ON customers (city);

EXPLAIN QUERY PLAN SELECT name FROM customers WHERE lower(city) = 'lisbon';
EXPLAIN QUERY PLAN SELECT name FROM customers WHERE city = 'Lisbon';

Wrapping the indexed column in a function hides it from the index. Either compare the raw column, or create an expression index on the same expression: CREATE INDEX … ON customers (lower(city)) (PostgreSQL, SQLite, MySQL 8, Oracle).

Dropping an index

DROP INDEX idx_orders_status_date;

Try it yourself

Compare the query plan for a product search before and after adding an index on category.