SQL Interview Preparation—Intermediate Level/

...

/

Answer: Creating an Index

Answer: Creating an Index

Find a detailed explanation of creating an index in SQL.

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