Composite Index

Hook: Interviewers love this topic because it separates 'I know indexes exist' from 'I know how the database actually finds rows'.

Question: What is a composite index, and why does the column order matter?

Answer: A composite index is one index built on two or more columns. The database stores the key values in order, so it can quickly search by the first column, then the second, and so on. This is useful when your common queries filter or sort on the same column combination, such as customer_id plus order_date.

Interview-Ready Answer: I think of a composite index as a sorted list on multiple columns, not separate indexes glued together. The order matters because most databases can use the leftmost part of the index most effectively, so a query like WHERE customer_id = ? AND order_date >= ? can be very fast, while a query on only order_date may not benefit much. A good composite index can also help with ORDER BY and can even become a covering index if it contains all the columns the query needs.

What it is

Detailed Explanation: A composite index, sometimes called a multi-column index, is an index whose key is made from several columns in a fixed order. The index is usually stored as a balanced tree, most commonly a B-tree (a tree that stays shallow so lookups are fast). The important idea is that the database does not store the columns as separate piles; it stores one ordered sequence like (a, b, c).

How it works under the hood

1. The database takes each row and builds an index key from the indexed columns, for example (tenant_id, created_at).
2. It sorts those keys lexicographically, meaning it compares the first column first, then the second if the first is tied, then the third, and so on.
3. When your query filters on the leftmost column, the engine can jump straight to the matching region of the tree instead of scanning the whole table.
4. If the query also filters on the next column with equality, the search narrows further. For example, tenant_id = 42 AND created_at = '2026-01-01' is a tighter match than just tenant_id = 42.
5. If the query uses a range on an earlier column, such as created_at >= '2026-01-01', the engine often cannot keep using later columns for direct positioning in the same way. This is the classic gotcha.
6. If the query only asks for columns already inside the index, the engine may answer from the index alone. That is called a covering index, which means the table itself does not need to be read for those rows.

Why column order matters

Think of the index like a phone book sorted by city, then street, then house number. If you know the city, it is easy to jump into the right section. If you only know the house number, the book is much less helpful. That is why the leftmost column is usually the most important one for search and sort patterns.

In practice, the best order is driven by your most common query shape. A common rule is: put columns used in equality filters first, put the range column later, and put sort columns where they match your ORDER BY. For many workloads, the best index for WHERE a = ? AND b = ? AND c > ? ORDER BY d is not random; it is chosen to match how the query narrows rows.

Composite index vs other options

Separate indexes are not a magic replacement. Some databases can combine them in special ways, such as bitmap or index-merge plans, but that is usually not as efficient as one well-chosen composite index for a tight combined lookup.

Performance notes interviewers like

• Lookup time is usually about O(log n + k), where n is rows in the index and k is the number of matching rows returned.
• Building an index from scratch is roughly O(n log n) because the engine must organize all keys.
• Every extra index adds write cost. Inserts, updates, and deletes must also maintain the index, so too many wide indexes slow writes and use more disk.
• For small tables, the planner may still choose a full scan because reading the whole table is cheaper than using the index.

Important edge cases

1. If the query only filters on the second column, the composite index may not help much unless the database has a special feature like skip scan. Do not rely on that in a design answer.
2. If the first indexed column is a wide range, the later columns often stop being useful for narrowing the scan.
3. Very wide text columns make large, heavy indexes. Indexing a long string plus another long string can cost a lot of storage and cache space.
4. Order direction can matter for ORDER BY. Some engines can scan an index backwards, but you should still design for the query pattern you actually need.

Memory check: a composite index is not just more columns; it is one sorted path with a left-to-right rule.

Real-World Example: Imagine a checkout service for a SaaS platform with millions of payment rows. The team shows merchants recent payments with a query like WHERE tenant_id = ? AND created_at >= ? ORDER BY created_at DESC LIMIT 50. A composite index on (tenant_id, created_at DESC) lets the database jump directly to one tenant’s recent rows and stop after 50 matches, which keeps the page fast even as the table grows.

Now the bug story: an engineer accidentally created (created_at, tenant_id) because the screen was sorted by date first. The app worked in testing, but in production the database had to scan huge date ranges across many tenants before it could filter by tenant. Symptoms showed up as p95 latency jumping from under 100 ms to several seconds, slow query logs filling with the same statement, and CPU spiking on the primary database. Users saw the payment history page spinner forever, and support tickets started saying, 'My dashboard loads, but my recent transactions are missing or delayed.'

The fix was simple but important: redesign the index to match the actual filter order used by the query. Once the leftmost column matched tenant_id, the index became a narrow, efficient path instead of a nearly useless sorted list.

Follow-up & Tricky Questions:

• How do you choose the column order in a composite index? Put the columns in the order that matches your most common query pattern: usually equality filters first, then range filters, then sort columns. The exact best order is workload-driven, not a fixed rule.
• Can one composite index replace two single-column indexes? Sometimes, but not always. A composite index is best for queries that use the same column combination, while separate single-column indexes can still be useful for unrelated queries.
• Does a composite index help ORDER BY? Yes, if the sort order matches the index order and the filter uses the leftmost prefix. Then the database may avoid a separate sort step.
• What is the leftmost-prefix rule? It means the index is most usable from the first column onward. If the first column is missing from the filter, the index is usually much less effective.
• What is a covering index? It is an index that contains every column needed by the query, so the engine can answer from the index alone without reading the base table row.
• Can a database use the second column if the first is missing? Usually not efficiently. Some engines have special plans like skip scans, but in an interview you should assume the leftmost column matters.
• If the first column is a range condition, can later columns still help? They may still help with filtering after the scan begins, but they usually do not narrow the search as strongly as equality on the first columns.
• Should I always put the most selective column first? Not always. Selectivity matters, but query shape matters more; an equality column that appears in every request is often more valuable first than a slightly more selective column that is rarely filtered first.

Tricky / gotchas:

• Can two separate single-column indexes behave exactly like one composite index? No. Some databases can combine them, but that is a different plan and usually not as efficient as a well-designed composite index for a tight multi-column lookup.
• Does the index help if I query only by the second column but the table is small? The optimizer may still choose a table scan because the scan is cheaper. Indexes are not always used just because they exist.
• Does adding more columns to the index always make it better? No. Bigger indexes cost more disk and slower writes, and extra columns only help if they match real queries.

Common Mistakes:

• Putting columns in the wrong order. Correction: order the index to match the most common filter and sort pattern, especially the leftmost equality columns.
• Thinking separate indexes are the same as a composite index. Correction: they are different plans; a single composite index is usually better for a combined predicate.
• Indexing too many columns because it feels safer. Correction: every extra index adds storage and write overhead, so keep the index focused on real queries.
• Ignoring ORDER BY and only thinking about WHERE. Correction: the same composite index can often satisfy both filtering and sorting if the order is chosen well.

Memory Hook: Think of a composite index like a filing cabinet sorted by department, then team, then name. If you know the department, the search is fast; if you start with the name only, you are digging through the wrong drawers.

Cheat Sheet:

• Composite index = one index on multiple columns.
• Column order matters because of the leftmost-prefix rule.
• Equality columns usually go before range columns.
• Good for WHERE, sometimes for ORDER BY, and sometimes for covering reads.
• Separate single-column indexes are not the same thing.
• More indexes speed reads but slow writes and use more disk.

Practice Tasks:

• Create a table with tenant_id, created_at, and status, then add a composite index and check the plan for a query with both filters.
• Swap the column order in the index and compare which queries still benefit.
• Add a third column to make a covering index, then measure how the query plan changes for a read that needs only indexed columns.