SQL Interview Preparation—Intermediate Level/

...

/

Answer: Creating Composite Index

Answer: Creating Composite Index

Find a detailed explanation of creating composite index in SQL.

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 ...