Composite Index vs Separate Indexes: When to Use Each

Use a composite index when your queries frequently filter or sort by multiple columns together in a predictable order; use separate indexes when columns are queried independently or in varying orders. This article explains the trade-offs and helps you decide based on your workload.

Formula

The calculator estimates the storage size of an index based on table size and number of indexed columns. The formula is:

Index Size (MB) = (rows × columns × bytes_per_entry) / (1024 × 1024)

Where:

• rows = number of rows in the table (millions)
• columns = number of columns in the composite index (or total columns across separate indexes)
• bytes_per_entry = 8 bytes (typical for a 64-bit integer key, adjust for your data types)

This gives a rough estimate; actual size depends on data types, page compression, and index fill factor.

Worked Example

Using the defaults: 1000 queries per day, 3 columns, 10 million rows.

Index size = (10,000,000 × 3 × 8) / (1,048,576) ≈ 228.88 MB

The calculator shows approximately 229 MB for both composite and separate indexes (since the formula uses the same inputs). In reality, separate indexes on the same columns would take roughly the same total space, but a composite index can sometimes be more compact due to prefix compression.

Background: How Database Indexes Work

An index is a data structure (usually a B-tree) that speeds up data retrieval by allowing the database to quickly locate rows matching a condition. A composite index is a single index that includes multiple columns, ordered left to right. Separate indexes are individual indexes on single columns.

The key property of a composite index is that it can satisfy queries that filter on a prefix of its columns. For example, an index on (last_name, first_name) can speed up queries filtering by last_name alone, or by last_name and first_name, but not by first_name alone. This is known as the “leftmost prefix rule.�

How to Choose: Query Patterns Matter

Your decision hinges on the queries your application actually runs. Consider these scenarios:

• Queries filter on multiple columns together – e.g., WHERE status = 'active' AND created_at > '2024-01-01'. A composite index on (status, created_at) is ideal.
• Queries filter on columns independently – e.g., sometimes WHERE status = 'x', sometimes WHERE created_at > 'y'. Separate indexes on each column let the database use either one (or combine them with a bitmap or index merge).
• Queries filter on multiple columns but in different orders – e.g., WHERE a = 1 AND b = 2 and also WHERE b = 2 AND a = 1. A composite index on (a,b) won’t help the second query if the planner doesn’t reorder conditions; separate indexes might be better.
• Queries sort by multiple columns – e.g., ORDER BY last_name, first_name. A composite index can avoid a filesort.

Common Problems and Misconceptions

One common misconception is that a composite index is always better because it “coversâ€� multiple columns. In reality, a composite index is useless for queries that don’t use its leftmost column. For example, an index on (a, b) does nothing for WHERE b = 5.

Another issue is index size and maintenance overhead. Every index slows down INSERT, UPDATE, and DELETE operations because the database must update the index. A composite index can be larger than a single-column index, but two separate indexes might be even larger in total. The real cost is the added write overhead and storage.

Comparing Composite vs. Separate Indexes

• Query speed: Composite indexes are faster for queries that filter on the exact column set (or a prefix). Separate indexes are faster for queries that filter on a single column.
• Storage: A composite index on (a, b) typically uses less space than two separate indexes on a and b, because it stores only one entry per row (with combined keys) rather than two. However, the size depends on data types.
• Write performance: More indexes = more write overhead. A composite index is one index to update; two separate indexes are two.
• Flexibility: Separate indexes are more flexible because they can be used independently or combined (e.g., index merge). A composite index is rigid – you must plan for the column order.

Costs and Trade-offs

Creating a composite index costs:

• Storage: Even if smaller than separate indexes, it still consumes disk and memory.
• Write overhead: Every insert/update must maintain the index.
• Planning complexity: You must anticipate which column combinations will be queried.

Separate indexes cost:

• More storage (usually).
• More write overhead (two indexes to update).
• Less query speed for multi-column filters (unless the database uses index merge, which is often slower than a single composite index).

Alternatives and Advanced Options

If you need to query on multiple columns in unpredictable ways, consider these alternatives:

• Index merge: Some databases (e.g., MySQL) can combine separate indexes using a union or intersection. This can work but is often slower than a composite index.
• Covering indexes: A composite index that includes all columns needed for a query (including SELECT columns) can eliminate table lookups entirely.
• Partial indexes: Index only a subset of rows (e.g., WHERE status = ‘active’) to reduce size and overhead.
• Expression indexes: Index on a function or expression (e.g., LOWER(email)) for case-insensitive searches.

Frequently Asked Questions

Can I use both composite and separate indexes on the same columns?

Yes, but it’s usually redundant. If you have a composite index on (a, b), a separate index on (a) is unnecessary because the composite index already covers queries on a alone. A separate index on (b) might still be useful if you query on b alone.

Does column order in a composite index matter?

Absolutely. The leftmost column is the most important. Put the column with the highest selectivity (more unique values) first, but also consider query frequency. The index can only be used for queries that filter on the leftmost column, then the next, and so on.

How many columns should a composite index have?

Typically 2 to 4 columns. More columns make the index larger and slower to maintain. If you need more than 4 columns, reconsider your query design or use a covering index only if necessary.

What is the “leftmost prefix rule�?

It means a composite index on (a, b, c) can be used for queries that filter on a, on a and b, or on a, b, and c – but not on b alone, c alone, or b and c. The query must use a contiguous prefix of the index columns.

How do I know if my database is actually using an index?

Use the query plan (EXPLAIN in MySQL/PostgreSQL, or the execution plan in SQL Server/Oracle). It shows which indexes are used. If an index isn’t being used, it might be because the query isn’t leftmost-prefix compatible, or the optimizer thinks a full scan is cheaper.

Does a composite index help with ORDER BY?

Yes, if the ORDER BY columns match the index order (and all are in the same direction). For example, an index on (last_name, first_name) can sort by last_name and first_name without a filesort. But ORDER BY first_name alone won’t use it.

Related

• Phone Battery Draining Fast After Changing a Setting
• Open Source vs Commercial AI Models: Which Is Better?
• Microsoft Copilot Agents: What They Actually Do

in t | Query frequency | queries per day | 1000 | 0 | 1000000
in cols | Columns in composite | count | 3 | 1 | 10
in rows | Table size | million rows | 10 | 1 | 1000
out idx_size | Composite index size | MB | rows * 1000000 * cols * 8 / 1024 / 1024 | 0
out sep_size | Separate indexes size | MB | rows * 1000000 * cols * 8 / 1024 / 1024 | 0