Search⌘ K
AI Features
Home
Courses
SQL Interview Preparation—Intermediate Level

Answer: Creating Composite Index

Understand how to create composite indexes on multiple columns to enhance SQL query performance. Learn to use profiling tools to measure query execution time and explore alternatives like optimizer hints and temporary tables for query optimization. This lesson equips you with practical skills to manage indexes and monitor their impact on database efficiency.

We'll cover the following...

Solution

The solution is given below:

MySQL
-- Query to create composite index
CREATE INDEX idx_sales_month_eid ON Sales (EID, Month);
-- Code to see the performance of the query 
SET    PROFILING = 1;
SELECT * FROM Sales WHERE Month = 'January' AND EID = 1;
SHOW   PROFILES;

Code explanation

The explanation of the solution code is given below:

  • Line 2: The CREATE INDEX statement creates an index named idx_sales_month_eid on the EID and Month columns in the Sales table.

  • Line 5: The statement SET PROFILING = 1; is used to enable profiling. 

  • Line 6: The SELECT query retrieves all columns from the Sales table and the WHERE clause specifies the condition on which we want to retrieve the data from the table. 

  • Line 7: The SHOW PROFILES command displays a list of all queries executed in the current session, along with their Query_ID, Duration (execution time), and the query itself. This helps monitor the performance of queries.

Note: The following code can be used to check performance before and after indexing. You need ...