Choose an index for a query pattern, not just because a column appears in WHERE. Start with a real, costly query; match its filters, joins, ordering and output to the smallest useful index; then inspect the execution plan and measure the workload before keeping it. The examples below are candidates to test, not guarantees of faster execution.
How do you choose the right index for a SQL query?
Read the full query and the workload around it. An index can help locate rows, support a join, provide rows in a useful order, or supply the requested columns without as much table access. Those goals compete with storage and the work required to maintain indexes as data changes. SQL Server and MySQL both recommend designing around actual queries and data characteristics, rather than indexing columns by rote (Microsoft’s SQL Server index design guide; MySQL’s index guide).
For each candidate query, record how often it runs and how much it matters, then identify:
- Predicates: columns used to filter rows, and whether a condition is an equality comparison or a range.
- Joins: the columns used to match rows between tables.
- Ordering or grouping: the columns and order in
ORDER BYorGROUP BY. - Output: the columns the query actually returns or aggregates.
- Data shape: how many rows match and how values are distributed.
A predicate alone does not justify an index. A small table or a query that returns a large share of its rows may be faster to serve with a scan. MySQL explicitly notes that sequential reading can beat index access when most rows are needed (How MySQL Uses Indexes). A plan that shows an index seek or scan is useful evidence, but only measured query and workload behavior tells you whether that choice helped.
#1 Best Overall
What order should columns be in a composite index?
A composite index stores an ordered key, so the leading column or columns determine which searches can use its prefixes. MySQL documents that an index on (a, b, c) supports lookups using (a), (a, b) and (a, b, c), but not a lookup on (b) alone. SQL Server gives the same practical warning: an index starting with LastName is not a useful match for a query searching only FirstName (MySQL’s multiple-column index documentation; SQL Server index design guide).
For a common starting point, put columns used by recurring equality filters early, then consider a range or ordering column. This is a hypothesis to test, not a universal formula: selectivity, range conditions, sort direction, joins and competing queries can change the best key order. PostgreSQL’s multicolumn behavior has its own planner rules; consult the documentation for the PostgreSQL version you run and verify with its plan (PostgreSQL multicolumn indexes).
Equality filter followed by ordering
Suppose a query retrieves a customer’s orders newest first:
SELECT order_id, created_at, total_amount
FROM orders
WHERE customer_id = ?
ORDER BY created_at DESC;A candidate key is (customer_id, created_at), because it starts with the equality condition and then includes the ordering column. Test direction and plan behavior on the target engine and version; the key is not a promise that the optimizer will avoid a sort or that execution will be faster.
Equality filter followed by a date range
For a query such as WHERE status = ? AND created_at >= ?, test a key beginning with status and followed by created_at. Compare it with alternatives against realistic data: if a status value matches most rows, a different order or no index may be more useful. Also consider whether the same index helps other frequent query shapes.
Keep predicates searchable
Compare compatible data types and avoid wrapping an indexed column in a transformation when a direct comparison will express the same condition. MySQL warns that conversions and incompatible types or character sets can prevent index use in some comparisons (How MySQL Uses Indexes). If the plan does not use the candidate index, inspect the exact predicate and its types rather than assuming the index definition is wrong.
When should you use a covering index?
A covering index makes the columns needed by a query available from the index itself, potentially reducing access to the base table. It can be worthwhile for a frequent query with a small, stable set of output columns, but adding payload columns makes an index wider and increases storage and modification work. Microsoft cautions against covering indexes with too many columns for those reasons (SQL Server index design guide).
For the orders example, a candidate index for the customer-and-date query could include order_id and total_amount as payload columns. Whether that trade-off pays off depends on the table, query frequency and write workload.
What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
- SQL Server: put search, join, and useful ordering columns in the key; use
INCLUDEfor columns needed only in the output where a covering index is justified. - PostgreSQL: supported index types can use
INCLUDEfor non-key payload columns. An index-only scan is possible, not guaranteed: visibility-map state can still require heap reads. See PostgreSQL’s index-only scan documentation. - MySQL: coverage depends on whether the index contains the columns the query needs. Do not copy SQL Server’s
INCLUDEsyntax as if it were MySQL syntax.
Should you index every column in a WHERE clause?
No. A separate index for every predicate is not automatically useful, and separate single-column indexes are not always equivalent to one composite index whose order matches a recurring query. MySQL may choose an index or use Index Merge in some cases, but the actual plan decides; its documentation describes how composite indexes support leftmost prefixes (Multiple-Column Indexes).
Every additional index consumes storage and adds work when indexed values change. An index that helps one read can slow writes or compete with other indexes for resources. Before adding one, check for existing indexes with the same leading columns or substantial overlap. Add one candidate at a time where operationally practical, then keep, revise or remove it according to representative read and write behavior.
When is a filtered or partial index a fit?
If an important query repeatedly targets a well-defined subset of rows, an index restricted to that subset may be smaller than a full-table index. The feature and syntax are engine-specific; do not assume a design transfers unchanged between products.
- SQL Server: a filtered nonclustered index can represent a defined subset. For example, a query consistently reading active orders could motivate a candidate filtered on
status = 'active'. Confirm that the query predicate aligns with the filter and inspect the plan. See Microsoft’s index design guide. - PostgreSQL: a partial index stores rows matching its predicate. The planner must be able to establish that the query condition implies the index predicate, so keep them compatible. See PostgreSQL partial indexes.
- MySQL: do not apply SQL Server filtered-index or PostgreSQL partial-index syntax by analogy. Check the documentation for your MySQL version and use an ordinary index design appropriate to the query.
How do SQL Server, MySQL and PostgreSQL differ?
The core process—start from the query, propose a key, inspect a plan, and measure impact—is shared. The syntax and optimizer behavior are not interchangeable. The table summarizes the documented distinctions relevant to ordinary B-tree index design.
Best Value
| Design question | SQL Server | MySQL | PostgreSQL |
|---|---|---|---|
| Composite key order | Leading key matters; a key starting with one column is not a match for a search on a later column alone. Guide | Usable leftmost prefixes: (a,b,c) supports prefixes, not (b) alone. Manual |
Use PostgreSQL’s multicolumn rules and inspect the target version’s plan. Documentation |
| Covering retrieval | Nonclustered indexes can add non-key payload with INCLUDE. Guide |
An index can cover a query when it contains the required columns. Manual | Index-only scans and INCLUDE are available in supported cases; heap reads can still occur. Documentation |
| Index for a subset | Filtered index. Guide | Do not assume equivalent filtered/partial-index syntax; consult version-specific documentation. | Partial index with a predicate the planner can match to the query. Documentation |
| Plan inspection | Execution plans; Query Store and index usage views can help validate workload behavior. Guide | EXPLAIN shows the chosen plan and key details. Manual |
EXPLAIN shows the selected plan; pair it with representative execution measurements. Documentation |
| Cost to account for | Storage, I/O, memory footprint and index maintenance. | Space and maintenance for inserts, updates and deletes; scans can win when most rows are needed. Manual | Storage and write maintenance, evaluated alongside PostgreSQL-specific plan behavior. |
The versioned documentation consulted for this article was SQL Server 17, MySQL Reference Manual 26.7 and PostgreSQL current documentation resolving to PostgreSQL 18, checked on 2026-10-04 UTC. These are documentation versions, not a claim about the release installed on your system. Use documentation matching your server version; PostgreSQL’s index chapter also covers specialized index types and operator classes (PostgreSQL Chapter 11: Indexes).
Independent reader supportYour contribution helps us test, update, and keep practical guides available for everyone.Why is the database not using the index?
First establish what the optimizer chose and how that plan behaved for representative executions. A scan is not automatically a mistake: small tables and queries returning many rows may favor sequential access. If an index appears unused, work through these checks:
- Inspect the predicate. Check for a function or conversion applied to the indexed column, and confirm compared values use compatible types. MySQL documents cases where conversions or incompatible types/character sets interfere with index use (How MySQL Uses Indexes).
- Check key order. A composite index may not serve a query that filters only on a non-leading key column.
- Check the result size and data distribution. If the query reads most of the table, a scan may cost less than following the index and fetching rows.
- Inspect the complete plan. Verify which key, if any, is selected; whether sorting or table access remains; and how actual runtime compares for representative executions. In PostgreSQL, use
EXPLAINand its execution options as appropriate (Using EXPLAIN). - Reassess workload fit. Check whether the proposed index serves the actual frequent query or only a hypothetical one, and account for write cost before retaining it.
A practical index-design workflow
- Choose a slow or expensive query from the real workload; note its frequency and business importance.
- Map its filters, joins, sort or grouping requirements, and selected columns.
- Propose the smallest key that fits the query shape, including a composite order when several columns recur together.
- Add output-only columns for coverage only when the potential reduction in table access justifies a wider index.
- Check existing indexes for duplication or useful overlap. Consider filtered or partial indexes only when a stable subset and the engine’s matching rules support them.
- Create or alter one candidate at a time when practical, then inspect the engine’s plan and measure representative query and write behavior.
- Keep, revise or remove the index based on observed workload benefit—not just because a plan mentions it.
For details beyond ordinary B-tree patterns—such as operator classes or specialized index types—use documentation for the exact engine and version rather than transferring assumptions between products.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
