Improving Query Efficiency with Generated Columns and Indexes in MySQL

Optimize Query with Generated Column and Index

Write a MySQL query to add a generated column based on the OrderDate column in the Orders table and create an index on the new column to optimize queries filtering by month.

Solution:

-- Add a generated column for the month extracted from OrderDate and index it.
ALTER TABLE Orders 
ADD OrderMonth INT AS (MONTH(OrderDate)) STORED,
ADD INDEX idx_order_month (OrderMonth);

Explanation:

• Purpose of the Query:
• To create a computed column that extracts the month from OrderDate.
• To improve performance of queries filtering by month through indexing.
• Key Components:
• ADD OrderMonth INT AS (MONTH(OrderDate)) STORED : Creates a generated column for the month.
• ADD INDEX idx_order_month (OrderMonth) : Indexes the new column to speed up month-based queries.
• Real-World Application:
• Useful in monthly sales reports or seasonal analysis where filtering by month is frequent.

Notes:

• Generated columns must be defined as STORED to be indexed.
• Ensure the function used (MONTH) meets the query requirements.

For more Practice: Solve these Related Problems:

• Write a MySQL query to add a generated column based on the RegistrationDate column in the Customers table and create an index on the new column to optimize queries filtering by year.
• Write a MySQL query to add a generated column based on the AddedDate column in the Products table and create an index on the new column to optimize queries filtering by quarter.
• Write a MySQL query to add a generated column based on the StartDate column in the Projects table and create an index on the new column to optimize queries filtering by week.
• Write a MySQL query to add a generated column based on the TransactionDate column in the Transactions table and create an index on the new column to optimize queries filtering by day.

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

Previous MySQL Exercise: Analyze Query Performance on a Partitioned Table with EXPLAIN.
Next MySQL Exercise: Analyze Derived Table Query Performance with EXPLAIN.