SQL

Composite Index in SQL

🧩 A Composite Index is an index created on two or more columns of a table. Instead of indexing a single column, it stores combinations of multiple column values, allowing the database to efficiently execute queries that filter, sort, or join using those columns together.

📖 What is a Composite Index?

A composite index (also called a multi-column index or compound index) combines multiple columns into a single index. It is particularly useful when queries frequently use the same set of columns in their search conditions.

Information

The order of columns in a composite index is extremely important because it determines which queries can efficiently use the index.

🎯 Why Use a Composite Index?

Composite indexes improve performance when multiple columns are commonly used together in SQL queries.

📌 Speed up multi-column searches.
📌 Improve filtering with multiple WHERE conditions.
📌 Optimize sorting using ORDER BY.
📌 Improve table joins involving multiple columns.
📌 Reduce the need for multiple single-column indexes.

📊 Sample Table

StudentID	Name	Department	City
101	Alice	Computer Science	Chennai
102	Bob	Computer Science	Madurai
103	Charlie	Mathematics	Chennai

📝 Basic Syntax

Composite Index Syntax

CREATE INDEX index_name
ON table_name (column1, column2, ...);

💡 Create a Composite Index

Create an index using both the Department and Name columns.

Create a Composite Index

CREATE INDEX idx_department_name
ON Students (Department, Name);

This index stores combinations of Department and Name, making related searches more efficient.

🔍 Query That Benefits from the Index

Search Using Both Indexed Columns

SELECT *
FROM Students
WHERE Department = 'Computer Science'
AND Name = 'Alice';

Since the query filters on both indexed columns, the database can efficiently use the composite index.

📚 Understanding Column Order

Suppose the following composite index exists:

Composite Index

CREATE INDEX idx_department_name
ON Students (Department, Name);

Query	Can Efficiently Use the Index?
WHERE Department = 'Computer Science'	✅ Yes
WHERE Department = 'Computer Science' AND Name = 'Alice'	✅ Yes
WHERE Name = 'Alice'	❌ Usually not efficiently by itself.

Important

A composite index is generally most effective when queries begin with the leftmost column(s) defined in the index. This is often referred to as the leftmost prefix principle.

📈 Composite Index for Sorting

Composite indexes can also improve sorting operations when the ORDER BY clause matches the indexed column order.

Filtering and Sorting

SELECT *
FROM Students
WHERE Department = 'Computer Science'
ORDER BY Name;

Because the index is organized by Department followed by Name, the database may avoid an additional sorting step.

📊 Single-Column Index vs Composite Index

Feature	Single-Column Index	Composite Index
Indexed Columns	One	Two or more
Best For	Single-column searches.	Multi-column searches.
Sorting Optimization	Limited.	Excellent when column order matches.
Storage Usage	Lower.	Higher.

💼 Real-World Example

An online shopping platform frequently searches products by Category and Brand. Creating a composite index on these columns significantly improves search performance.

Products Composite Index

CREATE INDEX idx_category_brand
ON Products (Category, Brand);

Queries such as the following can benefit from the index:

Product Search

SELECT *
FROM Products
WHERE Category = 'Laptop'
AND Brand = 'Dell';

🗄️ Database Compatibility

Database System	Composite Index Support
MySQL	✅ Fully supported.
PostgreSQL	✅ Fully supported.
SQL Server	✅ Fully supported.
Oracle	✅ Fully supported.
SQLite	✅ Supports multi-column indexes.

⚠️ Common Mistakes

❌ Choosing an inefficient column order.
❌ Creating composite indexes for columns rarely queried together.
❌ Creating multiple overlapping indexes unnecessarily.
❌ Ignoring additional storage and maintenance costs.

Warning

Composite indexes improve read performance but increase the cost of INSERT, UPDATE, and DELETE operations because the index must also be maintained.

⚠️ Best Practices

Best Practice

Create composite indexes only when multiple columns are frequently used together in queries. Place the most selective or commonly filtered leading column first based on your workload, avoid redundant indexes, review execution plans, and periodically monitor index usage to ensure they continue to provide value.

🚀 Key Points to Remember

📌 A composite index contains two or more columns.
📌 Column order is critical for index effectiveness.
📌 Composite indexes improve multi-column filtering and sorting.
📌 They can reduce the need for several separate indexes.
📌 They consume additional storage and require maintenance.
📌 Composite indexes are supported by all major relational database systems.

>>"A well-designed composite index turns common multi-column searches into fast and efficient lookups."

Summary

✅ A Composite Index is a powerful SQL optimization technique for queries that frequently use multiple columns together. By choosing the correct column order and indexing only meaningful column combinations, you can significantly improve filtering, sorting, and join performance while keeping index maintenance under control.