Database Index: What It Is and When to Use It

A database index is an extra data structure that keeps a table's rows ordered by a particular column or group of columns — much like the index at the back of a book, it lets you jump straight to the value you want instead of flipping through every page. As your application grows, the answer to "why did this query get slow?" is very often a missing or badly designed index. In this article I explain how indexes speed queries up, what they cost, and when you should actually use them, with concrete examples.

What happens without an index: the full table scan

When you search on a column that has no index, the database is forced to run a full table scan: it reads every row from start to finish and keeps the ones that match. On a 1,000-row table you'll never notice this, but on 5 million rows every query means reading millions of rows off disk.

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

If there is no index on email, the engine walks the entire table just to find a single row. The complexity is roughly O(n): time grows linearly with the row count. With an index, the lookup drops to O(log n) — a huge difference at millions of rows.

How an index really works: the B-tree

The classic index in relational databases is a B-tree (more precisely a B+tree). Values are placed in branches in sorted order; each lookup descends from the root toward the leaves, and at every step it eliminates more than half of the remaining candidates. That is why you reach your target value in just a handful of steps, even among millions of records.

• Equality lookups (=) and range lookups (<, >, BETWEEN) are fast on a B-tree.
• Sorting (ORDER BY) and grouping can come almost for free because the index is already ordered.
• Prefix searches like LIKE 'aslain%' benefit from the index, but LIKE '%aslain' (leading wildcard) cannot.

For searching inside text (full-text) or for geographic queries, other index types are used instead of a plain B-tree — for example FULLTEXT indexes for text.

Creating an index and measuring its effect

Creating a single-column index is simple:

CREATE INDEX idx_users_email ON users (email);

Don't guess whether it actually works — measure it. In MySQL and PostgreSQL the EXPLAIN command shows the query plan:

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

In MySQL output, a type of ALL means a full scan (bad); ref or const means an index is used (good). The rows value estimates how many rows the engine expects to scan; with the right index in place this number drops dramatically.

Composite indexes and the "leftmost" rule

When you query several columns together, use a composite index. Column order is critical, because the B-tree sorts values in the order you give:

CREATE INDEX idx_orders_user_status
  ON orders (user_id, status);

This index speeds up WHERE user_id = 5 and WHERE user_id = 5 AND status = 'paid'. But it does not speed up WHERE status = 'paid' on its own — this is the leftmost prefix rule. Think of a phone book sorted by last name then first name: knowing only the first name won't let you search quickly.

Tip: put the most selective column that you query with equality on the left, and put range conditions on the right.

The costs: indexes are not free

An index is not a magic button that fixes every problem; it has real costs:

• Writes get slower: every INSERT, UPDATE and DELETE must also update the relevant indexes. If a table has 10 indexes, every insert means 10 extra structures to maintain.
• They consume disk and memory: indexes take up separate space; many unnecessary indexes bloat the database and pollute the cache.
• They don't help at low selectivity: on a column with only two values (e.g. is_active), an index usually gives no benefit; the engine prefers a full scan anyway.

So the rule is simple: if reads are frequent and the condition is selective, index it; don't blindly add an index to every column.

Practical recommendations

• Primary keys (PRIMARY KEY) and unique constraints are already indexed; don't re-index them.
• Index the foreign-key columns used often in JOIN and WHERE conditions.
• Consider columns you constantly sort by with ORDER BY as index candidates.
• Turn on the slow query log to find slow queries, then verify with EXPLAIN.
• Identify and drop unused indexes; maintenance is part of optimization too.

Frequently Asked Questions

Is it a good idea to add an index to every column?

No. Unnecessary indexes slow down writes, consume disk space and can confuse the query planner. Index only the columns that are actually queried and have high selectivity, and confirm your decision with EXPLAIN.

Why didn't the index speed up my query?

Common causes: you wrapped the column in a function (WHERE YEAR(created_at) = 2026 disables the index), you broke the leftmost rule on a composite index, or the column's selectivity is too low. The EXPLAIN output usually reveals the reason.

Are a primary key and an index the same thing?

A primary key is a special index: it is both unique and non-nullable (no NULL), and in most engines it defines the table's physical ordering (the clustered index). So every primary key is an index, but not every index is a primary key.

Is your database slowing down and you can't find out why? I can analyze your queries, design the right indexing strategy and rewrite poorly performing queries. Get in touch for help.