Composite Index Column Order: How to Choose the Right SQL Index

Your orders endpoint is slow only for one tenant. The query filters by tenant and status, returns the newest rows, and looks harmless until the table reaches tens of millions of records. Adding an index with all three columns helps, but putting the same columns in a different order can turn a targeted lookup into a broad scan followed by a sort.

This guide explains how to choose composite index column order from the query shape, not a memorized rule. It extends database indexing with SQL examples, then prepares you for database isolation levels in the Backend Developer Interview Guide. For the broader foundation, visit the Database tag.

Table of Contents

What a Composite Index Actually Orders

A composite index, also called a multicolumn index, stores keys lexicographically. For an index on (tenant_id, status, created_at DESC), entries are grouped by tenant_id; within each tenant they are grouped by status; within each group they are ordered by newest created_at first. It is closer to a filing cabinet sorted by country, then city, then street than to three independent indexes bundled together.

That physical ordering is why the column list is a performance decision. The database can navigate directly to a narrow branch when the query constrains the leading keys. It cannot make the same direct jump to a trailing key when every possible leading value is mixed ahead of it. PostgreSQL documents this as efficient use of constraints on leading B-tree columns; MySQL describes the usable prefixes of a multicolumn index in the same way.

flowchart TD
    A[Incoming orders query] --> B{Filters include tenant_id?}
    B -->|Yes| C{Filters include status?}
    B -->|No| H[Large index portion may be scanned]
    C -->|Yes| D[Seek one tenant and status group]
    C -->|No| E[Scan tenant group]
    D --> F{Order by created_at DESC?}
    E --> F
    F -->|Matches index order| G[Return newest rows without sort]
    F -->|Does not match| I[Sort remaining rows]

    classDef flow fill:#e8f0fe,stroke:#1a73e8,stroke-width:2px,color:#000000;
    class A,B,C,D,E,F,G,H,I flow;

The planner can still use an index in ways that do not look like the ideal case. PostgreSQL can apply filters on later columns and may use skip scans in some distributions; other engines have their own optimizations. Treat the leftmost-prefix rule as the default mental model for ordinary B-tree lookup design, then verify the actual plan instead of claiming an index is unusable in every edge case.

The Leftmost-Prefix Rule

Suppose an order service has this index:

CREATE INDEX CONCURRENTLY idx_orders_tenant_status_created
  ON orders (tenant_id, status, created_at DESC);

The following query aligns with all three keys. It can seek into one tenant and status group, then walk entries in the requested order until LIMIT 50 rows have been found.

SELECT order_id, customer_id, total_amount, created_at
FROM orders
WHERE tenant_id = $1
  AND status = 'PLACED'
ORDER BY created_at DESC
LIMIT 50;

The same index is also useful for a query that has only tenant_id, because tenant_id is its leading prefix. It may read a larger portion of that tenant’s data, but it starts in the right part of the tree.

SELECT order_id, status, created_at
FROM orders
WHERE tenant_id = $1
ORDER BY created_at DESC;

By contrast, a query that filters only on status has no stable way to jump to a contiguous part of this index. Rows with status = 'PLACED' are spread across every tenant group. A query can mention indexed columns and still not get a selective index lookup.

SELECT order_id, tenant_id, created_at
FROM orders
WHERE status = 'PLACED';

Do not confuse the index definition with the order in which predicates are written in SQL. The optimizer may reorder WHERE conditions. What matters is the index key order and the predicates it can apply to narrow the index traversal.

Choose Keys From a Real Query

Start with the endpoint and its observed workload. An index designed from a table diagram often solves a query nobody runs; an index designed from a slow query, its frequency, and its required result order has a job to do.

For a multi-tenant dashboard, first record the dominant access path:

SELECT order_id, customer_id, total_amount, created_at
FROM orders
WHERE tenant_id = $1
  AND status = $2
  AND created_at >= $3
ORDER BY created_at DESC
LIMIT 100;

tenant_id and status are equality predicates. created_at is a range and also an ordering requirement. A strong first hypothesis is therefore:

CREATE INDEX CONCURRENTLY idx_orders_tenant_status_created
  ON orders (tenant_id, status, created_at DESC);

This is not a universal formula. If the service normally fetches every status for a tenant, leading with status adds little. If the time window is extremely selective but the result must be sorted on another field, putting range before sort can reduce rows read but introduce a sort. The winning order is the one that improves the important query at acceptable write cost.

A Useful Design Conversation

Ask these questions before creating an index:

1. Which endpoint or job is slow, and how often does it run?
2. Which predicates are equality checks, ranges, joins, or optional filters?
3. Does ORDER BY need a specific direction to avoid a sort?
4. How many rows match each predicate in production-like data?
5. Is this table read-heavy enough to justify extra index maintenance on writes?

That last question matters. Every inserted order and every update to an indexed column has to maintain the new structure. The goal is the smallest index set that supports important access paths, not a separate index for every visible column.

Equality, Range, and Sort Trade-offs

The common heuristic is equality keys first, followed by range or sort keys. It works because equalities establish a compact group before the index has to scan a range. The difficult part is choosing between sort and range when both matter.

Equality Before Range

For this query, tenant_id is a good leading key because it restricts the work to one customer’s data before the time range is evaluated.

SELECT order_id, created_at
FROM orders
WHERE tenant_id = $1
  AND created_at >= NOW() - INTERVAL '7 days';

CREATE INDEX CONCURRENTLY idx_orders_tenant_created
  ON orders (tenant_id, created_at DESC);

Reversing the index to (created_at, tenant_id) starts with every tenant’s rows from the last seven days. The engine can filter tenant afterwards, but it may have to inspect far more index entries first.

Sort Before Range When Returning the First Page Is Critical

Consider a support queue:

SELECT ticket_id, priority, updated_at
FROM tickets
WHERE account_id = $1
  AND updated_at >= $2
ORDER BY priority DESC, updated_at DESC
LIMIT 20;

An index on (account_id, priority DESC, updated_at DESC) can satisfy the account filter and ordering, then examine rows until it finds enough that pass the date filter. An index on (account_id, updated_at DESC, priority DESC) narrows the time range first but may need an explicit sort for priority.

Neither design wins by definition. If the date range returns a tiny fraction of tickets, range-first may be cheaper. If the query is a latency-sensitive first-page view and the date range is broad, sort-first can avoid expensive sorting. Benchmark both candidates with representative data and a realistic limit.

Direction Is Part of the Design

For multicolumn B-tree indexes, ascending and descending direction can matter when a query orders columns in mixed directions. An index can usually be scanned backward to reverse every key, but it cannot always transform (priority ASC, created_at ASC) into (priority DESC, created_at ASC) without a sort. Put the required directions in the definition when the plan proves they matter.

Selectivity Is Evidence, Not a Slogan

Selectivity describes how much a predicate reduces the candidate set. customer_id = 9132 may match a handful of rows; status = 'PLACED' may match 40 percent of the table. Starting a query with a selective equality can substantially reduce work, but “most selective first” is not a substitute for examining the complete access path.

For two equality predicates, either order can reach the same final key combination in many B-tree implementations. Their order still changes which prefix queries the index can serve. (tenant_id, status) is useful for tenant-only queries; (status, tenant_id) is useful for status-only queries. Pick the order that supports the important prefixes, joins, ordering, and workload distribution.

Statistics can also be stale or misleading. A planner may avoid an index because it estimates that a predicate matches many rows, even if a recent traffic shift changed the distribution. Refresh statistics through your database’s normal maintenance process, then compare estimated and actual rows in the plan before redesigning an index. This is one reason the next articles on locking and query diagnosis should be treated as production skills, not interview trivia.

Prove the Design With EXPLAIN

An index proposal is a hypothesis. EXPLAIN (ANALYZE, BUFFERS) in PostgreSQL shows whether the planner selected it, how many rows were estimated and actually read, where time went, and whether a sort occurred.

EXPLAIN (ANALYZE, BUFFERS)
SELECT order_id, customer_id, total_amount, created_at
FROM orders
WHERE tenant_id = 42
  AND status = 'PLACED'
ORDER BY created_at DESC
LIMIT 50;

For a healthy plan, look for an index scan or index-only scan that uses the intended index, a small actual row count before the limit, and no avoidable sort. Do not force the index with hints merely to make a benchmark look successful. A planner that chooses a sequential scan may be correct when the query needs a large share of the table.

Run a controlled comparison:

1. Capture the baseline plan, latency, rows, and buffer reads.
2. Create the candidate index safely for your engine and deployment policy.
3. Run the same query against representative data and parameters.
4. Compare p50 and p95 latency, write cost, index size, and plan stability.
5. Keep the index only if the measured benefit outweighs its permanent maintenance cost.

CREATE INDEX CONCURRENTLY is a PostgreSQL-specific example; it reduces blocking of writes during the build but has operational constraints of its own. Use your database’s documented online-index procedure rather than copying a command across engines.

When Separate Indexes Are Better

A composite index is not automatically better than individual indexes. If one workload frequently queries tenant_id alone, another queries status alone, and the combination is rare, two single-column indexes may be the clearer and cheaper choice. PostgreSQL can combine separate indexes with bitmap scans for some AND and OR queries, though that combination generally loses index ordering and may require a sort.

Avoid duplicate coverage without evidence. An index on (tenant_id, status, created_at) already has a leading tenant_id prefix, so a separate tenant_id index can be redundant. It can still be valuable if the single-column index is dramatically smaller and dominates the workload. Check actual usage and size before dropping anything.

Covering indexes are another deliberate trade-off. PostgreSQL can use INCLUDE columns to make an index-only scan possible without changing the searchable key order:

CREATE INDEX CONCURRENTLY idx_orders_tenant_status_created_covering
  ON orders (tenant_id, status, created_at DESC)
  INCLUDE (order_id, customer_id, total_amount);

This may save table reads for a hot list endpoint, but it enlarges the index and does not help if frequent updates prevent index-only visibility checks. Measure it; do not add INCLUDE columns simply because an article recommended them.

Production Rollout Checklist

Create indexes with the same caution as an application change. On a large write-heavy table, a poorly timed build can consume I/O, inflate replicas, and interfere with latency-sensitive traffic.

1. Confirm the query is important with traces or database statistics.
2. Capture its baseline plan and representative parameter values.
3. Check for existing indexes with an equivalent useful prefix.
4. Use the engine’s online or concurrent build procedure where appropriate.
5. Watch build progress, replication lag, disk space, write latency, and error rate.
6. Verify the post-build plan on production-like traffic instead of a single favorable parameter.
7. Document the query served by the index and revisit it when the endpoint changes.

For a query that still times out after the correct index, investigate the whole path: connection-pool saturation, N+1 application queries, a wide result set, missing pagination, lock waits, or a plan regression can all be the real bottleneck. Database connection pooling is a useful companion when the delay happens before SQL begins.

Interview Questions

1. How do you choose composite index column order?

I begin with one important query rather than the table schema. I put the equality predicates that establish the useful prefix first, then decide whether a range or ordering key should follow based on the measured cost of scanning versus sorting. I also check which prefix queries must remain fast, because (tenant_id, status) and (status, tenant_id) serve different standalone workloads. Finally, I validate the candidate with an execution plan and write-cost measurement.

2. Does the most selective column always go first?

No. Selectivity is useful evidence, especially for a leading equality, but it is not a universal ordering rule. With multiple equality conditions, key order may not change the final lookup much, while it strongly affects which leading-prefix queries are supported. Required sorting, range predicates, joins, and the actual mix of queries can make a less selective leading key the better design.

3. Can an index on (a, b, c) serve WHERE b = ??

Not as a normal selective leftmost-prefix lookup for a B-tree index. The database may still scan it, filter a later key, or use an engine-specific optimization such as a skip scan, but that is different from directly navigating to one b group. If b-only queries are important, evaluate a separate index beginning with b rather than relying on a coincidental plan.

4. Why can a range predicate limit later index keys?

Once the engine opens a range such as created_at >= ..., there may be many matching entries to scan. A later equality can be tested while scanning, but it does not necessarily shrink the initial range traversal the way a preceding equality does. Move stable equalities before the range when that matches the workload, then confirm the plan because behavior differs by engine and index type.

5. When would you use separate single-column indexes instead?

Use them when the application has several independent access paths and no one combined query is important enough to justify a wider index. Some engines can combine indexes for multi-predicate filters, but that can cost memory and lose ordering. A composite index is strongest when the same columns are repeatedly filtered and ordered together; separate indexes are often better for unrelated queries.

Conclusion

Composite index order is query design expressed in a B-tree. Start with the real filter, range, join, and sort pattern; use equality keys to define a useful leading prefix; then measure the range-versus-sort trade-off. The leftmost-prefix rule explains the common case, but a production decision still needs EXPLAIN, representative data, and an honest accounting of write cost.

Next, learn how database isolation levels change the correctness guarantees around the transactions those indexed queries read and write.

References

1. Multicolumn Indexes - PostgreSQL Documentation https://www.postgresql.org/docs/18/indexes-multicolumn.html
2. Multiple-Column Indexes - MySQL Reference Manual https://dev.mysql.com/doc/refman/5.7/en/multiple-column-indexes.html
3. SQL Server Index Design Guide - Microsoft Learn https://learn.microsoft.com/en-us/sql/relational-databases/sql-server-index-design-guide

YouTube Videos

1. “Composite Index: Why Column Order Matters in Query Optimization” - Engineering With Tannika https://www.youtube.com/watch?v=vO2IrYFZlSo