Database Indexing Explained for Application Developers

Most slow web apps aren't slow because of the programming language or the framework. They're slow because the database is reading far more data than it needs to. Indexes are the single biggest lever you have, and you don't need to be a DBA to use them well.

What an index actually is

Think of the index at the back of a book. Without it, finding every mention of "caching" means reading every page. With it, you jump straight to the right pages.

A database index is the same idea: a separate, sorted structure (usually a B-tree) that maps column values to the rows containing them. Without a suitable index, the database does a full table scan, checking every row. On 500 rows that's instant. On 5 million, it isn't.

Which columns need indexes

Start with the columns that appear in:

• WHERE clauses you run often: WHERE email = ?, WHERE status = 'active'
• JOIN conditions: foreign keys like orders.user_id
• ORDER BY on large result sets: ORDER BY created_at DESC
• Uniqueness rules: a unique index on users.email enforces the rule and speeds up lookups

Primary keys are indexed automatically. Foreign keys are not always. PostgreSQL does not automatically index foreign key columns, while MySQL's InnoDB does. If you're on Postgres, check those join columns.

Let the database tell you: EXPLAIN

Never guess. Ask the database how it plans to run your query:

EXPLAIN ANALYZE
SELECT * FROM orders WHERE user_id = 42 ORDER BY created_at DESC LIMIT 20;

What to look for:

• Seq Scan (Postgres) or type: ALL (MySQL) on a big table: a full scan, usually bad.
• Index Scan / Index Only Scan / ref: the index is being used.
• Rows examined vs rows returned. Reading 200,000 rows to return 20 means an index is missing or unusable.
• A separate sort step on a large set, which an index on the sort column could often eliminate.

Then add an index and run EXPLAIN ANALYZE again. The before and after numbers make the case for you.

Composite indexes: order matters

An index on multiple columns is sorted by the first column, then by the second within that, and so on. Like a phone book sorted by last name, then first name.

CREATE INDEX idx_orders_user_created ON orders (user_id, created_at);

This index helps:

• WHERE user_id = 42
• WHERE user_id = 42 ORDER BY created_at DESC
• WHERE user_id = 42 AND created_at > '2026-01-01'

But it doesn't help much with WHERE created_at > '2026-01-01' alone, because created_at isn't the leading column. This is the leftmost prefix rule. A useful default ordering: put columns tested for equality first, then range or sort columns.

Why your index isn't being used

You added an index and the query is still slow. Common reasons:

• Functions on the column. WHERE LOWER(email) = 'a@b.com' can't use a plain index on email. Use an expression index (CREATE INDEX ... ON users (LOWER(email))) or store normalised values.
• Leading wildcards. LIKE '%smith' can't use a B-tree index. LIKE 'smith%' can. For real text search, use full-text search features.
• Type mismatches. Comparing a text column to a number can force conversions that bypass the index.
• Low selectivity. An index on a boolean column where 95% of rows are true rarely helps. The planner may correctly decide a scan is cheaper. (Partial indexes help here: CREATE INDEX ... WHERE is_active = false.)
• Wrong column order in a composite index, as above.
• Outdated statistics. The planner relies on stats about your data. Running ANALYZE (Postgres) or ANALYZE TABLE (MySQL) refreshes them.

Indexes aren't free

Every index:

• Slows down writes. Each INSERT, UPDATE and DELETE has to update every relevant index.
• Takes storage, sometimes as much as the table itself.
• Needs maintenance and memory to be effective.

So don't index everything "just in case." Index for the queries you actually run, and periodically look for unused indexes. Postgres exposes usage statistics in pg_stat_user_indexes.

A practical workflow

1. Find slow queries: slow query logs, pg_stat_statements, or your APM tool.
2. Run EXPLAIN ANALYZE on the worst offenders.
3. Add the smallest index that fixes the plan (often a composite one).
4. Verify with EXPLAIN ANALYZE again.
5. Repeat for the next slowest query.

Also check you're not running the same query hundreds of times per request. That's the N+1 problem, and no index will save you from it. For the bigger picture, see why is my website slow?

FAQ

How many indexes is too many?

There's no fixed number. If a table is write-heavy and has indexes nobody's queries use, that's too many. Every index should map to real query patterns.

Does adding an index lock the table?

It can. On a large production table, use your database's online option, such as CREATE INDEX CONCURRENTLY in PostgreSQL, so normal reads and writes keep working while the index builds.

Should I index every foreign key?

Usually, yes, especially in PostgreSQL where it isn't automatic. Foreign keys are used in joins and in cascading deletes, and both get slow without an index.