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

Answer: Creating an Index

Explore how to create and apply indexes in SQL to enhance data retrieval speed. Understand SQL profiling techniques to monitor query execution and learn alternative optimization methods such as optimizer hints and temporary tables to improve performance.

We'll cover the following...

Solution

The solution is given below:

MySQL
-- Query to create an index on employee names
CREATE INDEX idx_employee_names ON Employees (EName);
-- Code to see the performance of the query
SET    PROFILING = 1;
SELECT * FROM Employees WHERE EName = 'John';
SHOW   PROFILES;

Code explanation

The explanation of the solution code is given below:

  • Line 2: The CREATE INDEX statement creates an index named idx_employee_names on the EName column in the Employees table.

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

  • Line 6: The SELECT query retrieves all columns from the Employees 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 in monitoring the performance of queries.

The following code can be used to check ...