Database performance improves when indexes follow real query shapes, selectivity, ordering, and write costs—not when every frequently mentioned column receives an index.
An index is a data structure maintained to answer particular questions efficiently. Treating it as a general performance switch leads to duplicate indexes, slower writes, larger backups, and queries that remain slow because the index does not match the operation.
Start with the query shape. Which rows are filtered? Which columns are joined? How are results ordered? Is the query returning one record, a page, an aggregate, or the newest records for a tenant? The combination matters more than the popularity of any single column.
For a compound index, column order reflects how the database can narrow and traverse the data. Equality conditions commonly come before range conditions and ordering, but the correct sequence depends on the query and data distribution. An index on tenant ID and status may help a tenant-scoped status list; reversing those columns may serve a different workload.
Selectivity affects value. A boolean with two values is often a weak index by itself because many rows still match. Combined with a tenant, date, or content type, it may become useful. The optimizer considers how much work an index avoids, not whether an indexed column appears in the query.
Ordering can remove expensive sorting when the index follows the filter and requested order. Cursor pagination can then continue from a stable indexed tuple such as publication time and unique ID. Offset pagination becomes increasingly expensive because the database still walks past earlier rows, and changing data can create duplicates or omissions between pages.
Covering indexes can answer a query from the index without returning to the table for every row, but adding many included columns increases storage and write work. The aim is not to cover everything. It is to improve a measured high-value path while keeping the structure maintainable.
Indexes have operational cost. Every insert, update, and delete may need to update several trees. A table with many overlapping indexes can perform poorly on writes even while appearing optimized for reads. Before adding an index, check whether an existing one already has the required leading columns and whether another can be consolidated safely.
Use execution plans with realistic data. Development databases often contain too few rows and too little variation to reveal the production plan. Examine estimated and actual row counts, access type, selected keys, sorting, temporary work, and time spent. A plan is evidence of what the database chose, not a ritual to attach to a ticket.
Application design also affects the query. Selecting unnecessary columns, loading relationships one row at a time, counting on every request, or filtering after retrieval cannot be repaired entirely with an index. ORM convenience should not hide the generated SQL or the number of database round trips.
Good indexing is a conversation between product behaviour, query design, and measured data. Name the question first, observe how it is currently answered, and add the smallest structure that changes the result.