Composite Indexes Interview Questions

Master Composite (Multi-Column) Indexes with interview-focused questions covering Leftmost Prefix Rule, Column Order, Selectivity, Query Optimization, Composite Primary Keys, Execution Plans, and production best practices.

Introduction

A Composite Index (also called a Multi-Column Index) is an index built on two or more columns.

Composite indexes are one of the most powerful database optimization techniques because many production queries filter on multiple columns.

A properly designed composite index can dramatically improve:

WHERE Queries
ORDER BY
GROUP BY
JOIN Operations
Covering Queries

However, choosing the wrong column order can make an index almost useless.

Understanding Composite Indexes is a must for Backend Developers, Database Engineers, and Solution Architects.

Composite Index Architecture

flowchart LR

SQLQuery --> QueryOptimizer --> CompositeIndex --> MatchingRows --> Application

1. What is a Composite Index?

Answer

A Composite Index is an index created on multiple columns.

Example

CREATE INDEX idx_employee

ON Employee

(Department, Salary);

The index stores both columns together.

2. Why do we need Composite Indexes?

Many queries filter using multiple columns.

Example

SELECT *

FROM Employee

WHERE Department='IT'

AND Salary>100000;

A composite index performs much better than scanning the table.

3. Composite Index vs Single Column Index?

Single Index	Composite Index
One Column	Multiple Columns
Limited Optimization	Optimizes Multi-column Queries
Smaller	Larger
Simple Queries	Complex Queries

4. How does a Composite Index work?

Database stores values in sorted order.

Example

Department

↓

Salary

↓

EmployeeId

The database navigates the tree using column order.

Composite Index Structure

flowchart TD

Root --> IT

Root --> HR

IT --> 50000

IT --> 100000

HR --> 45000

HR --> 85000

5. What is the Leftmost Prefix Rule?

The Leftmost Prefix Rule states

A composite index can be used only if the query starts with the leftmost indexed column(s).

Example

Index

(Department, Salary, EmployeeId)

Works for

Department

Department + Salary

Department + Salary + EmployeeId

6. Explain the Leftmost Prefix Rule with examples.

Index

(Department, Salary)

Works

WHERE Department='IT'

Works

WHERE Department='IT'

AND Salary>100000

Does NOT use the index efficiently

WHERE Salary>100000

Leftmost Prefix Rule

flowchart LR

Department --> Salary --> EmployeeId

Department --> IndexUsed

Salary

-.->IndexIgnored

7. Why is column order important?

The order determines

Search Efficiency
Index Usage
Query Performance

Changing column order changes how the optimizer can use the index.

8. How do you choose column order?

General recommendation

Equality Columns
High Selectivity Columns
Range Columns
Sorting Columns

9. What is Selectivity?

Selectivity measures

how unique

a column is.

Example

EmployeeId

↓

Very High

Gender

↓

Very Low

Higher selectivity usually gives better filtering.

10. Should high-selectivity columns always come first?

Not always.

The order should match

Application Query Patterns.

11. Can Composite Indexes support ORDER BY?

Yes.

Example

SELECT *

FROM Employee

WHERE Department='IT'

ORDER BY Salary;

Index

(Department, Salary)

No additional sorting required.

12. Can Composite Indexes optimize GROUP BY?

Yes.

Example

GROUP BY

Department,

Salary

Matching composite indexes reduce sorting operations.

13. Can Composite Indexes improve JOIN performance?

Yes.

Example

Employee.DepartmentId

=

Department.DepartmentId

Composite indexes reduce join cost.

14. What happens if query order differs?

Index

(Department, Salary)

Query

WHERE Salary>100000

AND Department='IT'

Optimizer usually rearranges predicates internally.

The important part is that the leading indexed column (Department) is present.

15. Can Composite Indexes be partially used?

Yes.

Index

(CustomerId, OrderDate, Status)

Query

WHERE CustomerId=100

Uses index.

Query

WHERE CustomerId=100

AND OrderDate>'2026-01-01'

Uses index.

16. When can't a Composite Index be used?

Example

Index

(CustomerId, OrderDate)

Query

WHERE OrderDate>'2026-01-01'

The leading column is missing.

17. Can one Composite Index replace multiple indexes?

Sometimes.

Instead of

CustomerId

OrderDate

Create

(CustomerId, OrderDate)

May eliminate separate indexes.

18. What is Index Selectivity in Composite Indexes?

Combined selectivity often provides better performance than individual indexes.

Example

Department

+

EmployeeId

More selective than

Department

alone.

19. Composite Index vs Multiple Single Indexes?

Composite	Separate Indexes
Better for Combined Queries	Better for Independent Queries
Lower IO	Optimizer May Merge
Faster Multi-column Search	Sometimes Less Efficient

20. What is Index Merge?

Some databases combine multiple single-column indexes.

Example

Department Index

+

Salary Index

However,

A Composite Index is usually faster.

21. What is a Covering Composite Index?

Contains every column required by the query.

Example

SELECT Salary

FROM Employee

WHERE Department='IT';

Index

(Department, Salary)

No table lookup required.

22. What is Composite Primary Key?

A Primary Key containing multiple columns.

Example

PRIMARY KEY

(CustomerId,

OrderId)

23. Banking Example

Query

SELECT *

FROM Transactions

WHERE AccountId=?

AND TransactionDate>?;

Index

(AccountId,

TransactionDate)

24. E-Commerce Example

Query

SELECT *

FROM Orders

WHERE CustomerId=?

ORDER BY OrderDate DESC;

Composite Index

(CustomerId,

OrderDate)

25. HR Example

Query

SELECT *

FROM Employee

WHERE Department='IT'

AND Salary>100000;

Composite Index

(Department,

Salary)

26. Logging Example

Query

WHERE

Application='Payments'

AND

LogDate>'2026-01-01'

Composite Index

(Application,

LogDate)

27. Common mistakes

Wrong Column Order
Too Many Composite Indexes
Ignoring Query Patterns
Indexing Low Selectivity Columns First
Duplicate Indexes

28. How do you verify Composite Index usage?

Use execution plans.

Examples

MySQL

EXPLAIN

PostgreSQL

EXPLAIN ANALYZE

SQL Server

Actual Execution Plan

29. Advantages

Faster Queries
Better Filtering
Better Sorting
Faster GROUP BY
Faster JOINs
Lower Disk IO

30. Disadvantages

Additional Storage
Slower INSERT
Slower UPDATE
Slower DELETE
More Maintenance

Composite Index Workflow

flowchart LR

Application --> WHEREClause --> Optimizer --> CompositeIndex --> MatchingRows

Enterprise Best Practices

Design indexes around query patterns.
Follow the Leftmost Prefix Rule.
Place equality columns first.
Avoid duplicate indexes.
Keep indexes as small as possible.
Monitor execution plans regularly.
Remove unused indexes.
Prefer composite indexes for frequent multi-column searches.
Test with production-scale data.
Review indexes after schema changes.

Quick Revision

Topic	Key Point
Composite Index	Multiple Columns
Leftmost Prefix Rule	Leading Column Required
Column Order	Very Important
Selectivity	Higher is Better
ORDER BY	Supported
GROUP BY	Supported
JOIN	Faster
Covering Index	Possible
Index Merge	Alternative
Execution Plan	Verify Usage

Interview Tips

Interviewers frequently ask

What is a Composite Index?
Explain the Leftmost Prefix Rule.
Why is column order important?
Composite Index vs Single Index.
Composite Index vs Index Merge.
Can Composite Index support ORDER BY?
Can Composite Index support GROUP BY?
How do you choose column order?
Give a banking example.
How do you verify index usage?

Always explain the Leftmost Prefix Rule with examples. This is one of the most frequently asked database interview questions and demonstrates a strong understanding of how query optimizers work.

Summary

Composite Indexes are one of the most effective database optimization techniques for multi-column queries. By storing multiple columns in a defined order, they enable efficient filtering, sorting, grouping, and joins while significantly reducing disk I/O.

Understanding the Leftmost Prefix Rule, column ordering, selectivity, execution plans, and real-world access patterns is essential for designing high-performance databases and succeeding in SQL, backend engineering, database engineering, and solution architect interviews.