Home » Courses » SQL Crash Course

SQL Indexes – Improving Query Performance

Lesson 12: SQL Indexes – Improving Query Performance

Estimated Read Time: 15-20 minutes

Objective: In this lesson, we will learn about SQL indexes and how they can significantly improve the performance of database queries. Indexes are used to speed up the retrieval of rows from a database table by providing a quick way to look up data.

1. What is an Index?

An index is a database object that helps speed up the retrieval of rows from a table. It works similarly to an index in a book, where you can quickly find the pages that contain information on a specific topic. In SQL, an index provides a faster way to search for records in a table by creating a sorted structure that can be queried quickly.

Indexes can be created on one or more columns in a table, and they can help optimize SELECT queries that use the indexed columns in their WHERE, JOIN, or ORDER BY clauses.

2. How Do Indexes Improve Performance?

When you perform a query without an index, the database has to search through every row in the table to find the data. This is called a full table scan, which can be slow, especially if the table contains many rows.

Indexes help by organizing the data in a way that makes it much faster to search. Instead of scanning the entire table, the database can use the index to quickly locate the relevant rows, making the query much faster.

3. Types of Indexes in SQL

There are several types of indexes in SQL:

Single-column Index: An index created on a single column of a table.
Multi-column (Composite) Index: An index created on multiple columns of a table.
Unique Index: Ensures that the indexed columns contain unique values.
Clustered Index: The table data is physically ordered based on the indexed column(s). Each table can have only one clustered index.
Non-clustered Index: The table data is not physically ordered by the indexed column(s), but the index stores a reference to the data.

4. Creating an Index

To create an index in SQL, you can use the CREATE INDEX statement. Here is the syntax:

CREATE INDEX index_name
ON table_name (column1, column2, ...);

Example: Creating a Simple Index

Query: Create an index on the EmployeeName column of the Employees table:

CREATE INDEX idx_employee_name
ON Employees (EmployeeName);

Result:

The index idx_employee_name will speed up queries that search for employee names.

5. Using Indexes in Queries

Once an index is created, the database will automatically use the index to optimize queries that use the indexed columns. Here is an example of a query that can benefit from an index:

Example: Query Using an Indexed Column

Query: Find all employees with the name ‘John Doe’ from the Employees table:

SELECT EmployeeID, EmployeeName, Salary
FROM Employees
WHERE EmployeeName = 'John Doe';

With the index on EmployeeName, the database will be able to find matching rows more quickly than if the index did not exist.

6. Unique Indexes

A unique index ensures that the values in the indexed columns are unique. This type of index is automatically created on columns that are defined with a UNIQUE constraint or a PRIMARY KEY constraint.

Example: Creating a Unique Index

Query: Create a unique index on the EmployeeID column:

CREATE UNIQUE INDEX idx_employee_id
ON Employees (EmployeeID);

This index ensures that each employee has a unique EmployeeID, and prevents duplicate employee IDs from being inserted into the table.

7. Clustered vs Non-clustered Indexes

Clustered Index: A clustered index determines the physical order of the data in the table. When you create a clustered index on a column, the table’s data is sorted by that column. Each table can have only one clustered index.

Non-clustered Index: A non-clustered index does not affect the physical order of the table’s data. It creates a separate structure that holds a reference to the data. A table can have multiple non-clustered indexes.

Example: Creating a Clustered Index

Query: Create a clustered index on the Salary column of the Employees table:

CREATE CLUSTERED INDEX idx_salary
ON Employees (Salary);

Example: Creating a Non-clustered Index

Query: Create a non-clustered index on the DepartmentID column:

CREATE NONCLUSTERED INDEX idx_department_id
ON Employees (DepartmentID);

8. Benefits and Drawbacks of Indexes

Benefits of Indexes:

Significantly improve the speed of data retrieval operations like SELECT.
Make sorting and searching faster, especially for large tables.
Improve performance of JOIN operations.

Drawbacks of Indexes:

Indexes consume additional storage space.
Slower performance for INSERT, UPDATE, and DELETE operations, as the indexes need to be updated when data changes.

9. Conclusion

Indexes are essential for improving query performance in SQL, especially when working with large tables or complex queries. By carefully choosing which columns to index, you can optimize the performance of your database and ensure fast data retrieval.

In this lesson, we covered:

What an index is and how it works.
Different types of indexes, such as single-column, multi-column, and unique indexes.
How to create and use indexes to speed up queries.
Clustered vs non-clustered indexes and their differences.
The benefits and drawbacks of indexes in SQL.

Now that you’ve learned about indexes, practice creating and using them to optimize your SQL queries and improve performance!

Next Lesson: SQL Transactions – Ensuring Data Integrity

SQL Crash Course