A composite index, also called a multi-column or concatenated index, is a database index whose key is made of two or more columns stored in a defined order. In database indexing, it lets the engine locate rows that match conditions on several columns at once, and it can also deliver rows already sorted in the order of its key, which removes separate sort steps from many queries.
The decisive property of a composite index is that column order matters. An index on (customer_id, created_at) is a different structure from an index on (created_at, customer_id), and each one serves a different set of queries. Designing composite indexes is therefore less about which columns to include and more about which column comes first.
How a composite index orders its keys
Most composite indexes are B-tree indexes whose entries are sorted lexicographically: first by the leading column, then by the second column within each value of the first, and so on. The structure resembles a telephone directory sorted by surname and then by first name.
This ordering produces the leftmost prefix rule. The engine can efficiently search on the first column alone, on the first and second columns together, or on any longer prefix, but it cannot directly seek on the second column alone, because its values are scattered across the whole index. A directory sorted by surname does not help find everyone named Anna.
| Query condition | Index usage | Reason |
|---|---|---|
| a = 1 | Full seek on the prefix | Leading column is constrained |
| a = 1 AND b = 2 | Seek on two columns | Longer prefix narrows the range |
| a = 1 AND b > 2 AND c = 3 | Seek on a and b; c only filtered | A range on b stops further seeking |
| b = 2 | No seek; at best a full index scan | Leading column is missing |
| a = 1 ORDER BY b | Seek and ordered read, no sort | Rows within a = 1 are sorted by b |
Choosing the column order
The most reliable rule is to place equality columns first and the range or sort column last. Once the engine meets a range condition such as greater than or BETWEEN, it can no longer use later columns to narrow the search, only to filter entries it reads.
Selectivity is the second consideration. Among several equality columns, the order affects search efficiency much less than many guides suggest, because the engine combines all equality values into one seek. Reuse across queries usually matters more: a column that appears in many different queries is a better leading column, because the same index then also serves those queries through the prefix rule.
| Query pattern | Recommended index | Why |
|---|---|---|
| Filter by tenant, sort by date | (tenant_id, created_at) | Equality first, sort column last |
| Filter by status and date range | (status, created_at) | Range column goes last |
| Filter by user, sometimes also by type | (user_id, type) | Prefix serves both queries |
| Join on order_id, then filter by product | (order_id, product_id) | Join key leads the seek |
Composite indexes compared with related index types
A composite index is one design option among several in indexing, and it overlaps with the covering index concept. The table compares it with the neighboring approaches that a query planner can choose between.
| Approach | Structure | Strength | Weakness |
|---|---|---|---|
| Composite index | One B-tree on several columns | Fast multi-column seeks and ordered reads | Useless without the leading column |
| Separate single-column indexes | One B-tree per column | Flexible for varied queries | Combining them via bitmap merge is slower |
| Covering index | Composite or INCLUDE columns holding all needed data | Index-only reads, no table access | Larger index, more write cost |
| Hash index | Hash of the full key | Fast equality on the full key | No prefix, range or ordering support |
A composite index becomes a covering index when it contains every column a query reads. PostgreSQL and SQL Server also support INCLUDE columns, which are stored in the leaf entries but are not part of the sort key, so they add coverage without affecting the order or the prefix rule.
Costs and limits of composite indexes
Every index slows writes and consumes storage, and composite indexes amplify both because their entries are wider. Each INSERT must add an entry to every index on the table, and each UPDATE that changes an indexed column must move the entry within the tree.
- Redundant indexes: an index on (a) is usually redundant when (a, b) exists, because the prefix already serves it.
- Too many columns: wide keys reduce the number of entries per page and deepen the tree; three or four key columns is a common practical ceiling.
- Engine limits: MySQL InnoDB allows up to 16 columns per index and PostgreSQL up to 32 by default, but such widths are rarely useful.
- Mixed sort directions: ORDER BY a ASC, b DESC needs an index declared with matching directions in many engines.
Diagnosing composite index problems
The execution plan shows whether the planner actually uses a composite index and on how many columns. EXPLAIN in PostgreSQL and MySQL reveals the index chosen, the conditions used as seek keys and the conditions applied only as filters.
| Symptom in the plan | Likely cause | Fix |
|---|---|---|
| Full table scan despite an index | Query omits the leading column | Reorder columns or add a suitable index |
| Extra sort step | Sort column not directly after equality columns | Move the sort column into position |
| Many rows read, few returned | Range column placed before equality columns | Put equality columns first |
| Index ignored for a function | Predicate wraps the column in a function | Use an expression index or rewrite |
Stripped to the essentials
A composite index is like a phone book sorted by last name and then first name. Finding every Smith is quick. Finding John Smith is even quicker. Finding everyone called John, whatever their last name, is slow, because the book is not sorted that way.
Databases work the same way. The order of the columns decides which searches become fast. A well-chosen composite index can turn a slow search through millions of rows into an almost instant lookup.
Where it is applied
A software-as-a-service product stores about 300 million events in one table shared by all customer accounts. The main dashboard query asks for the 50 latest events of one account, filtered by event type. The table had separate indexes on account_id and created_at, and the query took around 2 seconds for large accounts because the engine read every event of the account and then sorted them.
The team created a composite index on (account_id, event_type, created_at). Both equality columns now lead the key and the sort column comes last, so the engine seeks directly to the matching range and reads the newest 50 entries in order. The query dropped to a few milliseconds, and the old single-column index on account_id was removed as redundant, which recovered storage and reduced write cost.