Database indexes explained: how to make slow queries fast

Most “the app got slow” problems with a database have the same cause: a query that was fine with 1,000 rows now runs against 1,000,000, and the database is reading every one of them to find the few you asked for. An index fixes that, often turning a query from tens of milliseconds into a fraction of one.

The examples use SQLite, so you can try them on your own computer. PostgreSQL and MySQL work the same way, with slightly different commands for viewing query plans.

What an index is#

Think of a library card catalogue. To find every book by one author, you don’t walk the shelves; you open the drawer for that name and the cards tell you where each book is.

A database index is the same idea: a separate, sorted structure (usually a B-tree) that maps a column’s values to the rows that hold them. Because it’s sorted, the database can jump to the right place in a few steps instead of reading the whole table.

The primary key is indexed automatically. Other columns aren’t, unless you add an index or a UNIQUE constraint.

See the problem first#

Here’s an orders table with a million rows, and a query you’d run on a “my orders” page:

SELECT * FROM orders WHERE customer_id = 4217;

Ask the database how it plans to run it, by putting EXPLAIN QUERY PLAN in front:

EXPLAIN QUERY PLAN SELECT * FROM orders WHERE customer_id = 4217;

SCAN orders

SCAN means it reads every row. On my laptop, this took about 18 ms. That sounds small, but it grows with the table and runs on every page view.

Add the index#

CREATE INDEX idx_orders_customer_id ON orders (customer_id);

Run the same plan again:

SEARCH orders USING INDEX idx_orders_customer_id (customer_id=?)

SEARCH ... USING INDEX means it looks up the matching rows directly. The same query now takes about 0.02 ms: roughly a thousand times faster, and it stays fast as the table grows.

In PostgreSQL, use EXPLAIN or EXPLAIN ANALYZE and look for Seq Scan (reading everything) versus Index Scan. In MySQL, EXPLAIN shows type: ALL for a full scan.

Which columns to index#

Index the columns your queries search, join or sort by, in queries that run often:

• Foreign keys, such as orders.customer_id. You join and filter on them constantly. PostgreSQL doesn’t index them for you.
• Columns in common WHERE clauses, such as email on a login form or slug on a page lookup.
• Columns you sort by on big lists, such as created_at for “newest first”.

Don’t index everything “just in case”. Each index has a cost, covered below.

Indexes on several columns: order matters#

A query that filters by customer and status, newest first:

SELECT * FROM orders
WHERE customer_id = 4217 AND status = 'paid'
ORDER BY created_at DESC;

With only the customer_id index, the plan shows an extra step:

SEARCH orders USING INDEX idx_orders_customer_id (customer_id=?)
USE TEMP B-TREE FOR ORDER BY

It finds the customer’s rows, then sorts them separately. A composite index covers the whole query:

CREATE INDEX idx_orders_customer_status_created
  ON orders (customer_id, status, created_at);

SEARCH orders USING COVERING INDEX idx_orders_customer_status_created (customer_id=? AND status=?)

The sort step is gone, because the index already keeps each customer’s paid orders in date order. (“Covering” means every column the query needs is in the index, so the table itself isn’t read at all.)

A composite index works like a phone book sorted by surname, then first name. It helps when you search by surname, or surname and first name, but not by first name alone. So this index helps queries that filter on customer_id first; a query on status alone still does a full SCAN. Put the columns you filter by with = first, and the one you sort or range over last.

When an index can’t help#

The database skips the index when the query hides the column inside a calculation:

SELECT * FROM orders WHERE lower(status) = 'paid';   -- SCAN

The index stores status, not lower(status), so it can’t be used. Either store the value already normalised, or (in SQLite and PostgreSQL) create an index on the expression itself. The same applies to LIKE '%word' searches that start with a wildcard: a sorted index can’t find words in the middle of text. For that, use full-text search.

What indexes cost#

• Slower writes. Every insert, update and delete must also update each index on that table.
• Disk and memory. An index is a copy of its columns, kept sorted.
• Little gain on small tables. For a few hundred rows, a scan is already fast.

For a typical web app, which reads far more than it writes, a handful of well-chosen indexes is almost always worth it. Ten indexes on a table that receives a constant stream of inserts may not be.

How to find the queries that need one#

1. Turn on slow query logging (log_min_duration_statement in PostgreSQL, the slow query log in MySQL), or check your hosting dashboard.
2. Take the slowest, most frequent queries and run EXPLAIN on them.
3. Look for full scans on big tables, add the matching index, and check the plan again.

Indexes only help if the tables underneath are sound; database design basics covers that. And when a query is still expensive after indexing, caching is the next tool to reach for.

If your app or Wix site is slowing down under its own data, I can find the cause as part of custom API integration and backend work.