What is composite index

A composite index is an index on multiple columns in a database. It speeds up queries that filter by more than one column at once.

How it works: The database uses the composite index to quickly find rows that match multiple column conditions, rather than scanning the whole table.

When to use it: Use it when you frequently query with multiple columns together (like in WHERE, ORDER BY, or JOIN), but avoid overusing them as they can slow down insert/update operations.

A composite index is a type of database index that includes two or more columns of a table. It helps improve the performance of queries that filter or sort based on these multiple columns.

How Composite Index Works?

1. Index Creation: A composite index is created on a combination of columns. The order of columns in the index matters because the database uses a leftmost prefix rule to decide how the index can be used.
2. Query Optimization: The database uses the composite index to quickly locate rows that match the conditions involving the indexed columns. It works best when the query filters, sorts, or groups data based on the columns included in the composite index in the order they are defined.
3. Leftmost Prefix Rule: The index can be used if a query references the leftmost column(s) of the composite index. If the query doesn't include the first column, the index won't be fully utilized.

When to Use Composite Index?

Multiple Columns in WHERE Clause: When your queries frequently filter or search data using multiple columns.

1. Sorting on Multiple Columns: If queries use ORDER BY on multiple columns.
2. Joins on Multiple Columns: When joining tables using multiple columns as the key.
3. Performance Needs: When single-column indexes are insufficient for query optimization.

Example of Composite Index:

Consider a table Sales with the following columns:

• Region (Region of sale)
• Product (Product sold)
• SaleDate (Date of sale)
• Amount (Amount of sale)

Creating a Composite Index:

CREATE INDEX IX_Sales_Region_Product_Date
ON Sales (Region, Product, SaleDate)

How the Composite Index is Used?

1. Query 1 (Fully Utilizing the Index): The index works efficiently here because it uses all three columns.

SELECT * 
FROM Sales
WHERE Region = 'North' AND Product = 'Laptop' AND SaleDate >= '2024-01-01'

2. Query 2 (Leftmost Prefix Rule Applied): The composite index will still be used because Region and Product are the first two columns in the index.,

SELECT * 
FROM Sales
WHERE Region = 'North' AND Product = 'Laptop'

3. Query 3 (Skips Leftmost Column): The index will not be used effectively because the query skips the Region column, violating the leftmost prefix rule.

SELECT * 
FROM Sales
WHERE Product = 'Laptop'

Key Considerations:

• The order of columns in the composite index is critical.
• Composite indexes are beneficial for queries that use multiple columns in the correct order.
• Avoid creating unnecessary composite indexes, as they increase the storage and maintenance overhead.

If we create a composite index. would it be fall under clustered index or non clustered index?

Of course! A composite index in databases is a type of index that is created on multiple columns in a table. By creating an index on multiple columns, the database engine can optimize the retrieval of data based on multiple criteria simultaneously, improving query performance.

Here's how a composite index works: Let's say you have a table that stores information about customers, including their first name, last name, and email address. If you frequently run queries that filter data based on both the first name and email address, creating a composite index on both columns can speed up these queries significantly. The composite index stores information about both columns together, making it easier and faster for the database to locate the relevant data based on the specified criteria.

When to use a composite index: Composite indexes are particularly useful when queries involve multiple columns in the WHERE clause, JOIN conditions, or ORDER BY clauses. By using a composite index, you can avoid having separate indexes on each individual column and instead have a single index that covers all the necessary columns, reducing index storage space and improving query performance.

Here's a simple example in SQL syntax to create a composite index on two columns:

CREATE INDEX idx_name_email ON customers (first_name, email);

In this example, we are creating a composite index named "idx_name_email" on the "customers" table for the "first_name" and "email" columns.

Overall, using composite indexes can be a valuable optimization technique in database design, especially in scenarios where queries involve multiple columns and performance is a concern. I hope this explanation clarifies the concept of composite indexes for you! Feel free to ask if you have any more questions or need further clarification.