SQL Indexes and Query Performance Basics

A query that works correctly on 100 rows can crawl on 10 million. Indexes are the single biggest lever you have for making queries fast — and the single most over-applied tool when people don’t understand the trade-off. This post covers what an index is, when to add one, and how to look at a query plan to decide.

The running dataset

The users, orders, products schema. For this post imagine each table is much larger — users has a million rows, orders has fifty million. Indexes only really matter at that scale.

What an index actually is

An index is a separate data structure the database maintains alongside a table. It holds a sorted copy of the indexed column(s) plus a pointer back to the row.

The most common kind is a B-tree — a balanced search tree where each node holds a range of values and a pointer to a child node. Looking up a value is O(log n): the tree has depth ~20 even for a billion-row table.

You don’t need to understand the tree mechanics in detail. The intuition is enough:

• Without an index, finding “all rows where email = 'ada@example.com'” requires scanning every row in the table — a sequential scan.
• With an index on email, the database walks the tree to find the matching value, then follows the pointer to the row. A few page reads instead of millions.

That’s the whole pitch. Indexes turn linear scans into logarithmic lookups.

CREATE INDEX

The syntax is short:

CREATE INDEX idx_users_email ON users (email);

Conventions:

• Name them — idx_<table>_<column> is a sensible default.
• One index per CREATE INDEX statement.
• Index columns in the order you’ll filter or sort by.

Drop an index that turns out not to help:

DROP INDEX idx_users_email;

UNIQUE constraints automatically create an index — there’s no need (and no way) to add another one for email if it’s already declared UNIQUE. Primary keys are indexed automatically too.

What indexes help

Indexes accelerate four common patterns:

1. Equality filters — WHERE email = '...'.
2. Range filters — WHERE created_at > '2026-01-01'.
3. Sorting — ORDER BY created_at when the index matches.
4. Joins — joining on orders.user_id = users.id benefits from an index on orders.user_id.

The last one is the workhorse of real systems. Every foreign-key column should be indexed; many databases create that index automatically, but check yours — MySQL does, Postgres does not automatically index foreign keys.

CREATE INDEX idx_orders_user_id ON orders (user_id);
CREATE INDEX idx_orders_product_id ON orders (product_id);

Selectivity

The single most important word in indexing is selectivity — what fraction of the table’s rows a given value matches.

• A column where every value is unique (email) has perfect selectivity. An index lookup returns one row.
• A column with two values (is_active = true/false) is barely selective. An index lookup might return half the table — at which point a sequential scan is cheaper.

The rough rule: an index helps when the query touches less than ~5–10% of the rows. Beyond that the database typically chooses a sequential scan, even with the index available.

This is why indexes on boolean flags or status columns with three or four values rarely pay off on their own. They show up in multi-column indexes instead.

Multi-column indexes

An index on (a, b, c) is sorted first by a, then by b, then by c. It accelerates queries that filter:

• WHERE a = ?
• WHERE a = ? AND b = ?
• WHERE a = ? AND b = ? AND c = ?

It does not generally help queries that filter only on b or c. This is the “left-most prefix” rule.

CREATE INDEX idx_orders_user_status ON orders (user_id, status);

-- Uses the index
SELECT * FROM orders WHERE user_id = 1 AND status = 'paid';

-- Uses the index (left-most prefix)
SELECT * FROM orders WHERE user_id = 1;

-- Does NOT use the index efficiently
SELECT * FROM orders WHERE status = 'paid';

Column order matters. Put the most-selective column first when the workload allows.

When indexes hurt

Indexes aren’t free. Every INSERT, UPDATE, or DELETE that affects an indexed column has to update the index too. On a heavily-indexed table, writes get measurably slower.

Indexes also take disk space — sometimes hundreds of megabytes for a wide multi-column index on a large table. The page cache then has less room for the table itself.

Signs you’ve gone too far:

• A table has more index storage than data storage.
• Write latency increases noticeably after adding a new index.
• A query planner consistently picks a sequential scan because the indexed columns aren’t selective.

A small rule of thumb: index columns that show up in WHERE, JOIN, or ORDER BY on the queries you actually run. Don’t pre-emptively index every column.

Small tables don’t need indexes

A table with a few thousand rows is so small that scanning it costs less than walking an index. The query planner often ignores indexes on tiny tables. Don’t worry about it — focus your indexing effort on the tables with millions of rows.

EXPLAIN

EXPLAIN asks the database to describe how it would run a query. Every database supports it; the output format varies.

Postgres:

EXPLAIN SELECT * FROM orders WHERE user_id = 1;

A typical line:

Index Scan using idx_orders_user_id on orders  (cost=0.29..8.30 rows=2 width=44)
  Index Cond: (user_id = 1)

What to look for as a beginner:

• Seq Scan — sequential scan of the whole table. Fine on small tables; alarming on large ones if the WHERE clause should have used an index.
• Index Scan / Index Only Scan — the database used an index. Good.
• Rows — the planner’s estimate of how many rows survive each step. If reality differs by orders of magnitude, your statistics may be stale (ANALYZE refreshes them).
• Cost — relative cost. Useful for comparing two plans; the absolute number is not in any familiar unit.

EXPLAIN ANALYZE actually runs the query and reports real timings:

EXPLAIN ANALYZE
SELECT u.name, SUM(o.total) AS spent
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE u.country = 'US'
GROUP BY u.id, u.name;

Be careful with EXPLAIN ANALYZE on mutating queries — it really does run them. Wrap them in a transaction with BEGIN ... ROLLBACK to play safely.

Covering indexes

A covering index includes every column the query needs, so the database can answer the query from the index alone — without touching the table.

-- Query that often runs
SELECT user_id, total FROM orders WHERE status = 'paid';

-- Covering index
CREATE INDEX idx_orders_status_user_total ON orders (status, user_id, total);

Now the index has status, user_id, and total — enough to answer the query. Postgres calls this an “Index Only Scan.” It’s the fastest kind of read.

Covering indexes have a cost: the wider the index, the more storage and the slower the writes. Use them on hot, narrow queries you run constantly.

Indexes and function calls

A common gotcha: an index on a column doesn’t help a query that wraps the column in a function.

-- Uses idx_users_email
SELECT * FROM users WHERE email = 'ada@example.com';

-- Does NOT use idx_users_email
SELECT * FROM users WHERE LOWER(email) = 'ada@example.com';

The fix is to either store the column already lowercased, or create a functional index:

CREATE INDEX idx_users_email_lower ON users (LOWER(email));

Same gotcha applies to leading-wildcard LIKE (LIKE '%@example.com' doesn’t use a regular B-tree index — Postgres offers trigram indexes for this).

Partial indexes

Postgres and SQLite support partial indexes — an index on a subset of rows. Useful when most queries hit a small slice of a large table.

CREATE INDEX idx_orders_pending ON orders (created_at)
WHERE status = 'pending';

Smaller index, faster maintenance, perfect for “queue” tables where only a handful of rows matter at any moment.

A worked example

Suppose this query is slow:

SELECT u.name, o.id, o.total
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE o.status = 'paid'
  AND o.created_at >= '2026-01-01'
ORDER BY o.created_at DESC
LIMIT 50;

A reasonable index:

CREATE INDEX idx_orders_paid_created
ON orders (status, created_at DESC);

Why this index:

• status is the equality filter — leftmost column.
• created_at is the range filter and the sort — second column, descending to match ORDER BY DESC.
• The planner can satisfy the WHERE, the ORDER BY, and the LIMIT from the index, then look up the matching orders rows.

The join to users benefits from the existing primary-key index on users.id. That covers most of the cost.

EXPLAIN ANALYZE after the change should show an Index Scan and a much smaller total time. If it still chooses Seq Scan, the planner thinks the filter isn’t selective enough — usually because there aren’t enough rows in your test data.

Common beginner mistakes

• Adding indexes “just in case” on every column.
• Failing to index foreign-key columns and watching joins crawl.
• Indexing a low-selectivity column (is_active) on its own.
• Putting columns in the wrong order in a composite index.
• Wrapping the indexed column in a function (LOWER(email)) without creating a functional index.
• Treating EXPLAIN’s estimates as truth without running EXPLAIN ANALYZE.

Recap

You now know:

• Indexes are sorted lookup structures, usually B-trees
• CREATE INDEX accelerates equality, range, sort, and join queries
• Selectivity determines whether an index helps
• Multi-column indexes follow the left-most prefix rule
• Indexes slow down writes — don’t add them speculatively
• EXPLAIN and EXPLAIN ANALYZE show you what the planner does
• Covering and partial indexes are advanced tools for hot queries

A handful of carefully-chosen indexes typically gets you 95% of the way. The remaining tuning is about reading plans, watching real workload metrics, and adjusting from there.

Next steps

You’ve now seen the SQL language end to end — from SELECT through indexes. The last big question is: which database. Postgres, MySQL, and SQLite all speak SQL, but each has its own personality, dialect, and operational story.

Next: Postgres vs MySQL vs SQLite — Choosing a Database

Related reading: SQL JOINs, Subqueries and CTEs.

Questions or feedback? Email codeloomdevv@gmail.com.