SQL Indexes and Query Optimization

An index helps the database find rows without scanning the whole table, which is the useful definition until a query gets slow enough to require more detail.

PostgreSQL puts the tradeoff plainly: indexes can retrieve specific rows much faster, but they add overhead to the database and should be used sensibly. SQLite says the same thing from the planner side: SQL is declarative, so the database decides how to compute the result, and programmers give it useful indexes to choose from.

A Simple Index

CREATE INDEX users_email_idx ON users (email);

This helps a query that searches by the indexed email column:

SELECT id, email
FROM users
WHERE email = 'mark@example.com';

Without an index, the database may need to scan every row. With the index, it can look up the matching email more directly.

The mental model is an old one: an index is the book index at the back, not the chapter itself. The index tells you where to look, but the table still owns the row.

That distinction matters because an index is not magic storage. It is another data structure the database has to maintain. When the indexed value changes, the index changes too. When a row is inserted or deleted, the index has to stay in sync with it.

Index Types

For most application work, a B-tree index is the default starting point. PostgreSQL's index documentation describes B-tree as the fit for equality and range comparisons, which is why it works for ordinary predicates like =, <, >, BETWEEN, and ordered scans.

That maps cleanly to normal web queries:

SELECT id, email, created_at
FROM users
WHERE created_at >= DATE '2026-01-01'
ORDER BY created_at DESC;

A B-tree index on created_at can help because the query asks for an ordered range.

Hash indexes are built for a narrower job. They are built for equality lookups, not range scans or ordering. If all you need is "find the row where this value equals that value," a hash index may be relevant in databases that support it well. In most PHP/MySQL/PostgreSQL application work, I still start with the database's ordinary B-tree index unless a measured plan says otherwise.

Specialized indexes belong to more specialized jobs. PostgreSQL has GIN, GiST, BRIN, and other options for full-text search, arrays, ranges, geospatial work, and very large naturally ordered tables. Those are powerful tools, but they belong after the basic query shape is understood. If the query is a normal lookup, join, or ordered list, solve that normal problem first.

Choosing Indexes

Do not add indexes because a column "seems important." Add indexes because a query needs them.

Good candidates usually come from queries you can point to:

• Columns used in WHERE filters.
• Foreign keys used in joins.
• Columns used for common ORDER BY patterns.
• Composite keys that match real multi-column lookups.

Weak candidates usually come from anxiety rather than evidence:

• Tiny tables.
• Columns with only a few values, unless combined with another column.
• Columns that are written constantly but rarely searched.
• Every column in the table, which is a maintenance bill disguised as preparation.

The question I ask in review is blunt: show me the query. If the query is real and repeated, we can talk about an index. If the answer is "we might search by that someday," the index can wait until someday has a workload attached to it.

Composite Indexes

Column order matters because composite indexes are read from the left side outward.

CREATE INDEX orders_user_created_idx
ON orders (user_id, created_at);

This index fits a query that filters by user and sorts by creation time:

SELECT id, total_cents, created_at
FROM orders
WHERE user_id = 42
ORDER BY created_at DESC
LIMIT 20;

The same index may not help much if the query only filters by created_at. Think from the left side of the index outward.

That left-side rule is where many well-meant indexes go wrong, and the next example shows why the first column matters:

CREATE INDEX orders_status_created_idx
ON orders (status, created_at);

fits a query that starts with status:

SELECT id, total_cents
FROM orders
WHERE status = 'paid'
ORDER BY created_at DESC
LIMIT 50;

It is a weaker fit for a query that ignores status and only asks for the latest orders:

SELECT id, total_cents
FROM orders
ORDER BY created_at DESC
LIMIT 50;

The database may still use the index in some cases, but the index was not shaped for that query. If both queries matter, you may need a different index, a partial index, or a product decision about which path deserves the write cost.

Covering Indexes

A covering index contains enough columns for the database to answer the query from the index without visiting the table row, or with fewer table visits depending on the database and visibility rules:

CREATE INDEX orders_user_created_cover_idx
ON orders (user_id, created_at)
INCLUDE (total_cents, status);

That shape can support a narrow list query:

SELECT created_at, total_cents, status
FROM orders
WHERE user_id = 42
ORDER BY created_at DESC
LIMIT 20;

The indexed key columns are still the lookup and ordering path. The included columns are there so the database has the values the page needs. That is useful for high-traffic read paths, but it is not free. Included columns make the index larger, and larger indexes cost memory, cache, disk, backup time, and write maintenance.

If the page needs twenty columns, a covering index is probably the wrong answer. Fetch the row from the table and cover the small hot path, not the whole object.

Partial and Expression Indexes

Some useful indexes only cover part of a table. A partial index can target the rows a query actually uses:

CREATE INDEX orders_unshipped_created_idx
ON orders (created_at)
WHERE shipped_at IS NULL;

That can help an admin screen that repeatedly asks for unshipped orders:

SELECT id, created_at, total_cents
FROM orders
WHERE shipped_at IS NULL
ORDER BY created_at ASC
LIMIT 100;

The tradeoff is that the query predicate has to match the index predicate closely enough for the planner to use it. Partial indexes are excellent when a small active subset of the table gets most of the reads, such as open tickets, unpaid invoices, active sessions, pending jobs, or unshipped orders.

Expression indexes are useful when the query searches on a computed value:

CREATE INDEX users_lower_email_idx
ON users (lower(email));

That supports the matching expression in the query:

SELECT id, email
FROM users
WHERE lower(email) = lower('Mark@example.com');

Use expression indexes deliberately, because they often describe a real business rule. If the business rule is "emails are unique case-insensitively," the index may need to be unique, and the application should normalize input consistently. The database can help enforce the rule, but the rule still needs to be named.

Read the Plan

Use EXPLAIN to see the plan the database chose.

EXPLAIN
SELECT id, total_cents
FROM orders
WHERE user_id = 42
ORDER BY created_at DESC
LIMIT 20;

On shared hosting in the early 2000s, the raw output of a plain EXPLAIN was often all I had: no query analyzer, no dashboards, just reading the access type and deciding whether a sequential scan on a growing orders table was going to survive the week.

Use EXPLAIN ANALYZE when you need actual run-time information, but remember that it runs the query. PostgreSQL's documentation warns that side effects still happen for data-changing statements, so wrap dangerous tests in a transaction and roll them back.

BEGIN;

EXPLAIN ANALYZE
UPDATE orders
SET reviewed = true
WHERE created_at < now() - interval '1 year';

ROLLBACK;

That extra transaction is the difference between observing a plan and changing production data because you were curious.

Reading EXPLAIN Output

Plan output can look intimidating because it exposes the database's internal choices. Start with a few questions instead of trying to memorize every node type.

First, did the database scan the whole table or use an index?

Seq Scan on orders
Index Scan using orders_user_created_idx on orders

A sequential scan is not automatically bad. If the table is tiny, scanning it can be cheaper than using an index. If the query needs a large percentage of the table, a scan may be rational. The problem is a sequential scan on a large table when the page only needs a small slice.

Second, compare estimated rows with actual rows when using EXPLAIN ANALYZE. A plan that expected 10 rows and got 100,000 is operating with a bad picture of the data. That can mean stale statistics, skewed data, a predicate the planner cannot reason about well, or an index that does not match the query shape.

Third, look for sort work in the plan. A query can filter quickly and still slow down because ORDER BY forces a large sort after the filter. The right composite index can often handle both the filter and the order.

Finally, check how the plan orders its joins. If the plan multiplies rows early and filters late, the index may not be the first problem. Read SQL joins explained before tuning a join-heavy query in isolation.

Look for the Mismatch

When a query is slow, compare what you intended with what the planner did:

• Did it scan the whole table?
• Did it use the index you expected?
• Did it estimate 10 rows and read 100,000?
• Did a join multiply rows before filtering?
• Did ORDER BY force a sort after the filter?

The planner is usually rational with the statistics it has. If the stats are stale, the plan can look strange. If the query shape fights the indexes, the plan can be expensive while still being correct.

Statistics and Data Shape

The planner chooses a plan based on what it believes about the table. If the statistics are wrong, the plan can be wrong in a way that still looks logical.

This matters most when data is uneven. A status column with values like draft, paid, cancelled, and refunded may look low-cardinality, but the distribution can still matter. If 98 percent of orders are paid, an index on status may not help a paid query much. It may help a refunded query if that value is rare.

This is why real plans beat theory. Check the query that hurts, against data that resembles production, after the database has current statistics. Otherwise you are tuning a story about the data rather than the data itself.

Indexes Cost Writes

Every insert, update, and delete has to maintain the relevant indexes. On read-heavy tables, that cost is often worth it. On write-heavy tables, too many indexes can make the system slower in the name of speed.

This is the practical rule: add the index that supports a known query, check the plan, and stop before the schema turns into an index museum.

Indexes also cost operational simplicity over time. They take disk space, enlarge backups, slow some migrations, make bulk imports slower, and can hide a table-design problem long enough for that problem to become expensive.

There is a difference between "this table has many indexes because the product has many real read paths" and "this table has many indexes because nobody wanted to decide which query matters." The first is a cost of doing business. The second is technical debt with SQL syntax.

Tuning Workflow

When a page or report is slow, work in this order:

1. Identify the exact SQL and the exact user path.
2. Run the query against data that resembles production.
3. Read the plan with EXPLAIN or EXPLAIN ANALYZE.
4. Check whether the table shape and joins make sense.
5. Add or adjust one index that matches the measured query.
6. Re-run the plan and compare the result.
7. Remove redundant indexes once the better one is in place.

That last step is easy to skip. If a new composite index fully covers the old single-column index's job, the old one may be dead weight. Do not delete indexes casually, but do audit them. A database that only accumulates indexes eventually becomes slower to write, harder to migrate, and more expensive to operate.

PHP Symptoms

In PHP apps, missing indexes usually show up as slow admin pages, slow search filters, or reports that worked fine with 500 rows and fall over at 500,000. The PHP and MySQL with PDO layer did not suddenly become worse. The table got large enough for the database plan to matter.

When that happens, keep the stack in order:

1. Check the SQL.
2. Check the plan.
3. Add or adjust the index.
4. Only then start blaming the framework.

If the query joins multiple tables, read SQL joins explained before tuning the index in isolation. If every query needs a heroic index to survive, step back to schema design basics and check whether the table shape is doing the wrong job.

The same warning applies to AI-generated SQL. The generated query may be syntactically correct and still ask the database to do work in the most expensive possible shape. Review the table relationships, the filters, the order, and the plan before accepting the patch.

When one request writes related rows, pair indexing work with transactions and ACID. Fast reads do not compensate for half-written state. A serious database-backed app needs both: indexes that support the hot read paths and transactions that keep related writes coherent.

Frequently Asked Questions

Does a primary key create an index automatically?

Yes. Declaring a primary key creates a unique index on those columns in every mainstream database, so adding a second index on the same columns in the same order is redundant. Unique constraints also create an index that queries can use.

What is the difference between a clustered and a non-clustered index?

A clustered index determines the physical order of the rows, so a table has at most one. InnoDB clusters on the primary key. A non-clustered index is a separate structure holding the indexed columns and a pointer back to the row.

Should you index a column with only a few distinct values?

Usually not on its own. When a column such as a status flag matches a large share of the table, the planner often decides a scan is cheaper than the index lookups plus row fetches. Such columns are more useful as part of a composite index.

Do indexes help with ORDER BY and GROUP BY?

They can. An index already holds its columns in order, so the database may read them in sequence and skip a sort entirely. This only works when the ordering matches the index, including the direction and the leading columns.

Should you index foreign key columns?

Usually yes. Joins and lookups filter on those columns constantly, and some engines require an index to check the constraint efficiently. InnoDB creates one automatically for foreign keys; other databases may leave it to you.