What are SQL Indexes?

Introduction

An index is a unique lookup table in SQL databases that the database search engine can use to expedite data retrieval. When an index is built on a table’s columns, the database can locate rows considerably more quickly than in the absence of an index. See an index as a book’s reference guide; it will assist you in finding the information you require quickly and save you from reading the full text.

Overview

1. SQL indexes are unique lookup tables that expedite data retrieval, similar to a book’s reference guide.
2. Indexes significantly improve SQL query performance by allowing the database to locate rows quickly, preventing the need for full table scans.
3. Key types include composite (multiple columns), unique (ensures all values are unique), clustered (modifies physical order of data), and non-clustered (separate structure from data rows).
4. The SQL optimizer automatically chooses the appropriate index for a query based on parameters like query structure and table statistics.
5. In certain cases, you can specify a particular index using the USE INDEX hint if you know it is more efficient for your query.
6. This includes creating, verifying, and modifying indexes to maintain and optimize query performance, such as dropping, rebuilding, or disabling them.

Importance of SQL Indexes

Indexes are essential when it comes to improving SQL query performance. They are helpful when there is a lot of data in the database. In the absence of indexes, the database engine would have to run a full table scan, going over every row to see which ones satisfy the requirements of the query. It can take a long time to do this. Indexes allow the engine to find the relevant rows quickly, which speeds up the process considerably.

Types of Indexes

Here are the types of indexes:

• Composite Index: A composite index has several columns. It is helpful for queries that filter or sort based on many columns.
• Unique Index: Use a unique index to ensure that every value in the indexed column (s) is unique. This is frequently employed to guarantee that the main key column values are unique.
• Clustered Index: This index modifies the table’s physical order and performs key value-based searches. There can only be one clustered index per table.
• Non-Clustered Index: A Non-Clustered Index preserves a distinct structure from the data rows and does not change the table’s physical order. A table can have more than one non-clustered index.

In SQL, you can implicitly or explicitly specify which indexes to use in your queries. Let’s see them below:

Implicit Usage

When constructing indexes on a table, the SQL query optimizer automatically selects the appropriate indexes for a specific query. This decision is based on several parameters, including the query structure, table statistics, and index availability. Because the optimizer can typically determine which index to utilize best, this is the most popular and recommended technique for using indexes.

Explicit Usage

You may wish to force the query optimizer to use a particular index in certain situations. This can be helpful if you know that a specific index is more effective for your query or if you believe the optimizer is using the incorrect index because of out-of-date statistics or other factors. The index can be explicitly specified by using the USE INDEX hint.

Also read: SQL: A Full Fledged Guide from Basics to Advance Level

Types of indexes using a sample table

Let’s first create a table and sample it with sample records. 

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    Gender CHAR(1),
    Email VARCHAR(100),
    HireDate DATE
);

INSERT INTO Employees (EmployeeID, FirstName, LastName, Gender, Email, HireDate) VALUES
(1, 'John', 'Doe', 'M', '[email protected]', '2020-01-15'),
(2, 'Jane', 'Smith', 'F', '[email protected]', '2019-07-10'),
(3, 'Alice', 'Johnson', 'F', '[email protected]', '2021-03-22'),
(4, 'Bob', 'Williams', 'M', '[email protected]', '2018-11-30'),
(5, 'Charlie', 'Brown', 'M', '[email protected]', '2022-05-17');

Primary Key and Clustered Index

When a primary key is defined on a table in an SQL database, a clustered index is frequently automatically created in that primary key field. This indicates that the data rows are physically kept on the disc according to the values of the main keys.

Verification with SHOW INDEX

We can verify the existence of the primary key and associated index using the SHOW INDEX command

SHOW INDEX FROM Employees;

From the above image, we can see a key called PRIMARY under the column EmployeeID. 

Non-Clustered Index on LastName

CREATE INDEX idx_lastname
ON Employees (LastName);

The above code will create a non-clustered index in the column LastName. 

Unique Index on Email

CREATE UNIQUE INDEX idx_unique_email
ON Employees (Email);

The above code will create a unique key in the email column, and it will be named idx_unique_email.

Composite Index on FirstName and LastName

CREATE INDEX idx_composite_name
ON Employees (FirstName, LastName);

The above code creates a composite key using the columns FirstName and LastName. 

Query with Indexes

Here are queries with Indexes:

Query using LastName index

The below query will use a non-clustered index on LastName, thereby increasing the operation speed. 

EXPLAIN SELECT * FROM Employees WHERE LastName = 'Smith';

We have used the explain clause to learn about the query, and we can see that it uses the idx_lastname key. 

Query using the composite index on FirstName and LastName

EXPLAIN SELECT * FROM Employees WHERE FirstName = 'Jane' AND LastName = 'Smith';

The above image shows that it is using the key idx_lastname, but idx_composite_name can also be used. It will automatically pick the best key according to the query.

Query using the unique index on the Email

EXPLAIN SELECT * FROM Employees WHERE Email = '[email protected]';

In the above code, SQL will use idx_unique_email to query. 

Also read: SQL For Data Science: A Beginner Guide!

Managing Indexes

The code below can be used to drop a non-clustered index on LastName.

DROP INDEX idx_lastname ON Employees;

The code below can be used to rebuild the unique index on Email.

ALTER TABLE Employees DROP INDEX idx_unique_email;
CREATE UNIQUE INDEX idx_unique_email ON Employees (Email);

This code can disable the composite index on FirstName and LastName.

ALTER TABLE Employees DROP INDEX idx_composite_name;

Conclusion

In conclusion, the SQL query optimizer usually implicitly uses indexes, choosing the optimum course of action depending on the information. In some cases, explicitly declaring indexes with USE INDEX can be helpful; nevertheless, this should be done cautiously and usually as a last option. A useful tool for comprehending and improving index utilisation in your queries is the EXPLAIN statement.

Frequently Asked Questions

Badrinarayan M

Data science Trainee at Analytics Vidhya, specializing in ML, DL and Gen AI. Dedicated to sharing insights through articles on these subjects. Eager to learn and contribute to the field's advancements. Passionate about leveraging data to solve complex problems and drive innovation.