Indexing

Indexing is a technique used to improve the performance and speed of data retrieval operations in a database. It allows the database to find and access specific rows much faster, especially when working with large datasets. An index is created on one or more columns of a table and acts like a "lookup" to help locate the data quickly.

Indexes work similarly to a book's index: instead of reading through the entire book, you can quickly locate a topic by referring to the index.

Why Use Indexes?

Indexes are primarily used to:

1. Speed up query performance: Indexes reduce the amount of data the database needs to scan, making queries faster.
2. Improve data retrieval: Queries involving WHERE, JOIN, ORDER BY, and GROUP BY clauses can run much more efficiently when the relevant columns are indexed.

However, while indexes can improve data retrieval speed, they also come with trade-offs:

• Increased storage space: Indexes require additional disk space.
• Slower writes (inserts/updates/deletes): The database needs to update the indexes as data changes.

Types of Indexes in SQL

1. Single-Column Index: An index on a single column.
2. Composite Index (Multi-Column Index): An index on multiple columns.
3. Unique Index: Ensures that all values in the indexed column(s) are unique.
4. Full-Text Index: Used for text searching in large documents.

Creating Indexes

1. Single-Column Index

An index can be created on a single column to speed up queries that filter or sort by that column.

Syntax:

CREATE INDEX index_name ON table_name (column_name);

Example:

Create an index on the LastName column in the Employees table.

CREATE INDEX idx_lastname ON Employees (LastName);

Now, when you query using the LastName column, the database can use this index to quickly find the relevant rows.

2. Composite Index (Multi-Column Index)

A composite index is created on two or more columns. It's useful for queries that filter or sort on multiple columns.

Syntax:

CREATE INDEX index_name ON table_name (column1, column2);

Example:

Create an index on the LastName and FirstName columns in the Employees table.

CREATE INDEX idx_fullname ON Employees (LastName, FirstName);

This composite index will improve queries that filter or sort on both the LastName and FirstName columns together.

3. Unique Index

A unique index ensures that the values in the indexed column(s) are unique. It is often used to enforce data integrity.

Syntax:

CREATE UNIQUE INDEX index_name ON table_name (column_name);

Example:

Create a unique index on the Email column to ensure no two employees have the same email address.

CREATE UNIQUE INDEX idx_email ON Employees (Email);

This will prevent duplicate email addresses in the Employees table.

4. Full-Text Index

A full-text index is used for performing advanced text searches on large amounts of text data.

Syntax:

CREATE FULLTEXT INDEX index_name ON table_name (column_name);

Example:

Create a full-text index on the Description column in a Products table to support full-text search.

CREATE FULLTEXT INDEX idx_description ON Products (Description);

Full-text indexes are helpful for searching keywords within text fields, such as product descriptions or document contents.

5. Clustered Index

A clustered index determines the physical order of the rows in a table. There can only be one clustered index per table since data can only be physically sorted in one way.

Syntax:

CREATE CLUSTERED INDEX index_name ON table_name (column_name);

Example:

Create a clustered index on the EmployeeID column of the Employees table.

CREATE CLUSTERED INDEX idx_employeeid ON Employees (EmployeeID);

With this index, the rows in the Employees table are physically ordered by EmployeeID.

Dropping Indexes

If an index is no longer needed or if it affects performance negatively, it can be dropped.

Syntax:

DROP INDEX index_name ON table_name;

Example:

DROP INDEX idx_lastname ON Employees;