How index works in SQL?

What is an index in SQL?

In SQL, an index is a database structure that provides a fast and efficient way to retrieve data from a table. It works by creating a mapping of values in a specific column or set of columns to the locations of the corresponding rows in the table. This allows the database management system to quickly locate the rows that match a specific query, without having to scan the entire table.

When we create an index in SQL, the database management system sorts the values in the indexed column or columns, and then creates a data structure that maps these values to the corresponding rows. When a query is executed that includes a condition on the indexed column or columns, the database management system can use the index to quickly locate the matching rows.

How to create an index in SQL?

To create an index in SQL, you can use the "CREATE INDEX" statement. The syntax of the statement is as follows:

Syntax:

CREATE [UNIQUE] INDEX index_name
ON table_name (column1 [ASC|DESC], column2 [ASC|DESC], ...);

• index_name is the name you want to give to the index.
• table_name is the name of the table where you want to create the index.
• column1, column2, ... are the names of the columns that you want to include in the index. You can include multiple columns in an index, but keep in mind that the order of the columns can affect the performance of the index.
• ASC or DESC specify the sort order of the indexed columns. ASC means ascending order, while DESC means descending order.

CREATE INDEX idx_employee_name
ON employees (last_name, first_name);

This example creates an index named idx_employee_name on the employees table, based on the values in the last_name and first_name columns.

How does index work in SQL?

In SQL, an index works by creating a mapping of values in a specific column or set of columns to the locations of the corresponding rows in a table. This allows the database management system to quickly locate the rows that match a specific query, without having to scan the entire table.

When you create an index in SQL, the database management system will sort the values in the indexed column or columns, and then create a data structure that maps these values to the corresponding rows. This data structure is optimized for fast search and retrieval of data.

When a query is executed that includes a condition on the indexed column or columns, the database management system can use the index to quickly locate the matching rows.

The process works as follows:

• The database management system looks up the values in the index for the specified column or columns, and finds the location of the matching rows in the table.
• The database management system retrieves the matching rows from the table based on the locations found in the index.

By using an index, the database management system can avoid having to scan the entire table to find the matching rows, which can significantly improve query performance.

Example:

Here is an example to demonstrate how an index works in SQL. Let's say you have a table named employees with the following data:

Let's say you want to retrieve all the employees with a salary greater than 60,000. Without an index, the database management system would have to scan the entire employees table and check the value of the salary column for each row to find the matching rows. This would be a slow and inefficient process for large tables.

To improve the performance of this query, you can create an index on the salary column:

CREATE INDEX idx_salary
ON employees (salary);

Now, when you run the query to retrieve all employees with a salary greater than 60,000, the database management system can use the idx_salary index to quickly locate the matching rows:

SELECT *
FROM employees
WHERE salary > 60000;

The database management system would perform the following steps:

1. Look up the values in the idx_salary index for the salary column.
2. Find the location of the matching rows in the employees table.
3. Retrieve the matching rows from the employees table based on the locations found in the index.

The result of the query would be:

As you can see, by using the idx_salary index, the database management system was able to quickly locate the matching rows, without having to scan the entire employees table. This significantly improves the performance of the query.