SQL Indexes: How They Speed Up Queries (and What They Cost)

An index is a separate, sorted structure that lets the database find rows without reading the whole table. The analogy everyone uses is the index at the back of a book, and it is a good one: looking up a word takes seconds, and the index costs paper and has to be reprinted when the book changes.

This page covers what to index, why the column order in a composite index matters more than anything else, and the handful of query patterns that quietly stop an index being used.

Creating one

The syntax is the easy part:

CREATE INDEX idx_employees_dept ON Employees (dept_id);

SELECT first_name, dept_id
FROM Employees
WHERE dept_id = 20;

On twelve rows this changes nothing measurable — the database will read the whole table either way, because that is cheaper than consulting an index. Indexes matter at scale: on a million-row table, the difference between reading every row and jumping straight to the matching ones is the difference between a second and a millisecond.

What to index

Index the columns that appear in these places, in roughly this order of value:

1. Foreign key columns. Every join and every cascading delete uses them, and they are rarely indexed automatically.
2. Columns in your WHERE clauses. Especially selective ones — a column that narrows a million rows to ten.
3. Columns in ORDER BY. An index is already sorted, so the database can often skip the sort entirely.
4. Columns in GROUP BY. Same reason: grouping sorted input is cheap.

And do not index:

• Low-selectivity columns. An index on a yes/no flag rarely helps, because half the table matches and reading the table directly is faster than bouncing through an index.
• Columns you never filter or join on. An index on a description nobody searches is pure cost.
• Small tables. Below a few thousand rows the whole table is likely already in memory.
• Everything, on a write-heavy table. See the next section.

What indexes cost

Every index has to be kept correct, which means every INSERT, UPDATE and DELETE that touches an indexed column also updates the index. Five indexes on a table means an insert does six pieces of work instead of one.

So the honest rule is: add indexes in response to slow queries you have actually measured, not in anticipation. And look for indexes nobody uses — every database can report index usage, and an unused index is pure overhead on every write.

Composite indexes and why column order decides everything

An index can cover several columns, and the order you list them in is the single most important decision about it:

CREATE INDEX idx_orders_customer_status ON Orders (customer_id, status);

SELECT order_id, order_total
FROM Orders
WHERE customer_id = 101
ORDER BY order_total DESC;

Think of it as a phone book sorted by surname, then first name. That book is excellent for “everyone called Patel” and useless for “everyone called Asha”, because the Ashas are scattered throughout. An index on (customer_id, status) behaves the same way:

That last row surprises people: the order of conditions in your WHERE clause does not matter at all, only the order of columns in the index. The rule of thumb is to put the column you filter on with equality first, and the range or sort column second.

A composite index can also make a query covering — when every column the query needs is in the index, the database answers from the index alone and never touches the table. That is often the difference between fast and very fast.

Why my index is not being used

Almost always one of these, and all of them are worth recognising on sight:

• The column is wrapped in a function. WHERE LOWER(email) = 'asha@example.com' cannot use an index on email, because the index stores the original values, not the lower-cased ones. Either store the column already normalised, or create a function-based index on LOWER(email) where your database supports one.
• The condition is arithmetic on the column. WHERE salary * 12 > 100000 is unindexable; WHERE salary > 100000 / 12 is not. Keep the column alone on one side.
• A leading wildcard. LIKE 'Sys%' can use an index; LIKE '%Sys%' cannot, for the same reason you cannot use a book index to find words ending in a suffix. Full-text search exists for this.
• Type mismatch. Comparing a text column to a number makes the database convert one side, which usually loses the index.
• It is not the leading column of a composite index. See above.
• The table is small, or the filter is not selective. Here the planner is right and a full scan really is cheaper. This is the case people mistake for a bug most often.

The way to find out which of these applies is never to guess: ask the database. EXPLAIN (or EXPLAIN ANALYZE on PostgreSQL, EXPLAIN PLAN on Oracle, the execution plan in SQL Server) shows exactly which indexes a query used and how many rows it expected at each step.

EXPLAIN ANALYZE
SELECT order_id, order_total
FROM Orders
WHERE customer_id = 101;

Kinds of index worth knowing about

A partial index is the most under-used of these. If ninety per cent of a table is archived rows nobody queries, CREATE INDEX ... WHERE active = 1 indexes a tenth of the data and answers every query that matters.

A practical routine

1. Find the slow query — from a slow-query log or your monitoring, not from intuition.
2. Run EXPLAIN on it and read which step is expensive.
3. Index the column being filtered or joined on, choosing the leading column of a composite index deliberately.
4. Measure again. Keep the index only if it helped.
5. Periodically look for unused indexes and drop them.

That loop, applied to the two or three queries that actually matter, gets almost all the available benefit. Indexing every column in the schema gets you slower writes and a bigger database.

Practice this topic

Reading explains the idea; producing it yourself is what makes it stick. These exercises use exactly what this page covers, and your answer is checked by running it against the same sample database:

• Put a name to the departmentMedium
• Who is closing the deals?Hard
• Customers with a shipped orderMedium
• Biggest departments firstMedium

See all 42 exercises →

Frequently asked questions

Related reading

• Primary key vs foreign key
• SQL views and why they do not cache
• EXISTS vs IN and the index that makes either fast
• ORDER BY, LIMIT and the sort an index can remove
• SQL constraints: NOT NULL, UNIQUE, CHECK and keys