You can throw faster CPUs at a slow query, but you will still lose if the engine has to touch too many rows to answer something that should have been a tight lookup. Indexing is the discipline of turning “scan everything and hope” into “go directly to the small slice of data that matters.”
At a practical level, high performance indexing is constrained by three things you cannot escape. First, how selective your predicates really are. Second, how well your index order matches how queries filter and sort data. Third, how much write overhead you are willing to accept. Every index is a trade. Faster reads always come at the cost of slower writes, more storage, and more complexity for the optimizer.
This is why “index everything” quietly fails in production. You do not win by creating more indexes. You win by creating the smallest possible set of indexes that make your most important queries cheap.
Early research into real world database tuning reveals a consistent theme across engines and vendors. Indexes must be designed around how queries behave, not how tables are structured. Multicolumn indexes are powerful but easy to misuse. And many of the biggest wins come from reducing unnecessary reads, not from clever data structures. Taken together, the lesson is simple: index design is query design.
Index from the workload, not from the schema
Before creating any index, capture your real workload. That means production queries with their actual WHERE, JOIN, ORDER BY, and LIMIT clauses intact. Then ask two direct questions.
First, what is the database forced to do if no index helps? Usually the answer involves scanning large portions of a table, sorting, and performing expensive joins. Second, what would the ideal access pattern look like? Most of the time, it is a narrow seek into a small range of rows, read sequentially, then stop early.
Execution plans are your primary tool here. Whether you are using PostgreSQL, MySQL, or SQL Server, your goal is the same. Identify the operator that dominates cost and design an index that makes it irrelevant.
One important constraint to accept early is that “one index per query” does not scale. You want a compact index set that multiple queries can reuse. Otherwise, index maintenance becomes its own performance problem.
Choose index types based on data shape, not habit
B-tree indexes are the default for good reason. They handle equality, ranges, and ordering well. But they are not the answer to every problem.
Different data shapes reward different index strategies. Multicolumn B-tree indexes work well when predicates appear together consistently. Covering indexes help when queries need extra columns but should avoid repeated table lookups. Partial indexes shine when a small subset of rows dominates query traffic. Lightweight summary indexes become valuable when tables grow into the hundreds of millions of rows and data naturally clusters by time or ID.
The common thread is not sophistication, but avoidance. The best index is the one that lets the engine skip as much irrelevant data as possible.
Composite indexes only work when they match access patterns
Composite indexes are powerful and dangerous. Most performance regressions blamed on “the planner” are really caused by poorly ordered composite indexes.
Two rules consistently hold up in practice. First, composite indexes are only useful from the leading columns forward. If the first column is not constrained, the rest of the index rarely helps. Second, column order must reflect how queries filter and sort, not abstract ideas about selectivity.
A reliable starting heuristic is to place equality predicates first, followed by range predicates, and then any columns needed to support ordering. This allows the engine to narrow the search space quickly, scan in the desired order, and stop early when limits apply.
This is not a law. It is a hypothesis that must be tested with execution plans. But it is far more reliable than rules based purely on column cardinality.
Use covering and partial indexes to reduce unnecessary reads
Two of the most effective indexing techniques focus on reading less data rather than indexing more.
Covering indexes allow a query to be answered entirely from the index itself. The key columns support filtering and ordering, while additional non-key columns satisfy the select list. This avoids repeated lookups into the base table and can dramatically reduce latency for read heavy endpoints.
Partial indexes restrict indexing to rows that matter most. If a dashboard or API only cares about a small subset of rows, indexing everything else is wasted effort. By narrowing the index to frequently accessed data, you reduce size, improve cache residency, and lower write overhead.
These techniques are especially powerful when used together. A small, covering index on a hot subset of rows often outperforms a large, general purpose index by an order of magnitude.
Worked example: shrinking a 100 million row problem
Consider an events table with 100 million rows. Each row is roughly 200 bytes, putting the table around 20 gigabytes. An API endpoint filters by account, restricts results to the last seven days, sorts by timestamp descending, and returns the most recent 50 rows.
Without a suitable index, the database may scan large portions of the table, sort a massive result set, and then discard nearly everything. Even scanning ten percent of the table means reading gigabytes of data and performing expensive sorts.
A composite index on account ID and timestamp, ordered to match the query, changes the game. The engine can seek directly to the account, walk the timestamp range in order, and stop after reading 50 rows. No global sort. No wide scan. Just a small, predictable access pattern.
If the query needs a few extra columns, the index can be extended to cover them. If most rows are irrelevant due to soft deletes or status flags, the index can be limited to the active subset. Each refinement reduces the amount of data touched, which is where real performance gains come from.
A repeatable indexing process that avoids index sprawl
High performing teams treat indexing as an ongoing process, not a one time optimization.
Start by identifying the queries that matter most, based on both frequency and latency. Then examine their execution plans and isolate the dominant cost. Design the smallest index that eliminates that cost and verify that it actually changes the plan and improves tail latency.
Finally, revisit indexes periodically. Queries evolve. Features change. Indexes that were once essential can become pure write overhead if left unchecked.
This discipline prevents both under-indexing and the slow creep of unnecessary indexes that quietly tax every write.
FAQ
Should I use multiple single column indexes or one composite index?
If queries consistently filter on multiple columns together, a composite index usually performs better. Separate indexes only help when the engine can combine them efficiently, which is workload dependent.
How many columns is too many in a composite index?
Beyond three or four columns, returns diminish quickly unless query patterns are extremely consistent. Wide indexes cost more to maintain and are harder for planners to use effectively.
When are lightweight summary indexes useful?
They shine on very large tables where data naturally clusters, such as time series or append only logs. They are not a replacement for precise indexes, but they can dramatically reduce scan costs.
When should I aim to fully cover a query with an index?
When the query is latency sensitive, read heavy, and frequently executed. Covering indexes trade write cost for predictable reads, which is often a good trade in APIs and dashboards.
Honest Takeaway
Database indexing is not about clever tricks. It is about discipline. You study the workload, observe where time is really spent, and design indexes that let the engine avoid unnecessary work.
If you do not measure real plans and real latency, you will create indexes that feel right but do little. Start small, focus on one high impact query, and design an index that lets the database seek, read in order, and stop early. Repeat that process, and your system will feel fast long before you scale hardware.
Kirstie a technology news reporter at DevX. She reports on emerging technologies and startups waiting to skyrocket.
