Database Indexing Without the Guesswork

Stop guessing at indexes — read the query plan instead

Database indexing is where a lot of teams cargo-cult: they add an index because a slow query exists, sometimes it helps, often it does not, and nobody is quite sure why. The reliable approach is the opposite of guessing. Ask the database what it is doing with EXPLAIN, find the query that scans the most rows, and index for that specific access pattern. An index is a precise answer to a specific question your queries keep asking — not a sprinkle of magic over a slow table. Once you start treating indexes as answers to measured questions, the whole topic stops being mysterious and becomes a tractable engineering problem.

EXPLAIN is the only honest source of truth

Before adding any index, run EXPLAIN — or EXPLAIN ANALYZE for real timing — on the slow query. It shows whether the database is doing a sequential scan over the whole table or using an index, and how many rows it touches to produce the result.

• A sequential scan on a large table feeding a selective filter is your prime indexing candidate.
• An index scan that already exists means the slowness is elsewhere — perhaps a join or a sort, which the plan will also reveal.
• The row counts tell you whether an index will actually help: filtering a million rows down to ten is exactly what indexes are for; filtering a thousand down to nine hundred is not.

Run EXPLAIN again after adding the index to confirm the database actually uses it. Sometimes the planner ignores a new index because it judges a scan cheaper, and the only way to know is to look. Verifying the result closes the loop and stops you from believing an index helped when it changed nothing.

Index what you filter, join, and sort on

Indexes earn their keep on the columns your queries use in WHERE clauses, JOIN conditions, and ORDER BY. Look at your actual query workload, not your imagination.

• A column you frequently filter by exact match — a user id, a status, a tenant id — is a strong single-column index candidate.
• A column you sort by can be served directly from an index, letting the database skip a separate sort step entirely.
• Foreign-key columns used in joins almost always deserve an index, since unindexed joins force repeated full scans.

The foreign-key case is the one teams forget most often. Many databases index a primary key automatically but do not index the columns that point at it, so a join from the child side scans the whole table. If a page feels slow and it joins two large tables, an unindexed foreign key is a prime suspect worth checking first.

Composite index column order is not arbitrary

When a query filters on more than one column together, a composite index covering them can be far faster than two separate indexes — but the column order matters and is governed by a real rule. An index on two columns can serve queries that filter on the first column alone, or on both together, but not on the second column alone. So lead with the column you always filter by, and put the more selective column early. A query that filters by tenant and then by date wants the index ordered to match how the query narrows the data, not the order the columns happen to appear in the table.

This left-to-right rule is why one carefully ordered composite index often replaces several single-column ones. Map the index order to the shape of your most important query, and you get more speed from fewer indexes — which, as the next section explains, is exactly what you want.

Every index has a write cost

Indexes are not free. Each one must be updated on every insert, update, and delete to the underlying rows, which means every index you add slows writes a little and consumes storage. On a write-heavy table, a pile of speculative indexes can quietly become a bottleneck of their own.

• Add indexes deliberately, each justified by a real query you measured.
• Periodically check for unused indexes — most databases can report which indexes are never used — and drop them.
• Watch for redundant indexes, where one index is a prefix of another and the shorter one is doing nothing the longer one cannot.

The mental model is a balance: reads want more indexes, writes want fewer, and your job is to hold the smallest set that serves the queries that actually matter. An index nobody's queries use is pure cost — slowing every write and consuming storage while returning nothing.

Selectivity decides whether an index is worth it

An index helps most when the column has high selectivity — many distinct values, so a lookup narrows the result sharply. An index on a column with only two or three possible values, like a boolean flag, often will not be used at all, because scanning is cheaper than the index lookup when half the table matches. Understanding selectivity tells you in advance which indexes the database will actually choose to use, and saves you from adding ones it will quietly ignore. When you do need to filter on a low-selectivity column, a partial index that covers only the rare value is often the smarter move than indexing the whole column.

How BSH can help

BSH Technologies tunes databases for clients whose queries have outgrown their original schema, and we work from query plans and measurements rather than hunches. If your application is slowing down as data grows, or your write throughput is sagging under too many indexes, we can analyse your real workload, add the indexes that matter, remove the ones that do not, and leave you with a database that scales with your data. Reach out and we will start by reading the plan.