Improving Query Efficiency with Composite Indexes in MySQL

Create a Composite Index for Enhanced Performance

Write a MySQL query to create a composite index on the CustomerID and OrderDate columns in the Orders table.

Solution:

-- Create a composite index on CustomerID and OrderDate in the Orders table.
ALTER TABLE Orders ADD INDEX idx_customer_orderdate (CustomerID, OrderDate);

Explanation:

• Purpose of the Query:
• To create an index that optimizes queries filtering by both CustomerID and OrderDate.
• Enhances performance for multi-column searches.
• Key Components:
• ALTER TABLE Orders : Specifies the target table.
• ADD INDEX idx_customer_orderdate (CustomerID, OrderDate) : Defines the composite index.
• Real-World Application:
• Useful for reports or queries that analyze orders over time for specific customers.

Notes:

• The order of columns in the composite index is important for query optimization.
• Ensure that queries are designed to take advantage of the composite index for best performance.

For more Practice: Solve these Related Problems:

• Write a MySQL query to create a composite index on the `CategoryID` and `Price` columns in the Products table.
• Write a MySQL query to create a composite index on the `CustomerID` and `OrderDate` columns in the Orders table.
• Write a MySQL query to create a composite index on the `FirstName` and `LastName` columns in the Employees table.
• Write a MySQL query to create a composite index on the `ProductID` and `Quantity` columns in the OrderDetails table.

Go to:

• MySQL Query Optimization Exercises Home. ↩
• MySQL Exercises Home ↩

PREV : Remove an unused Index.
NEXT : Analyze JOIN Query Performance with EXPLAIN.

Have another way to solve this solution? Contribute your code (and comments) through Disqus.