What is Indexing and What are it's Types

When working with large databases, you might notice that retrieving data can sometimes be slow. This is where indexing comes into play. Indexing helps the database find and retrieve data faster, similar to how an index in a book helps you quickly find a topic. In this post, I’ll explain what indexing is and the different types of indexes in simple terms.

1. What is Indexing?

An index in a database is like a roadmap that helps you find specific information faster. Instead of scanning the entire table, the database can use the index to quickly locate the row you're looking for. It improves the performance of queries, especially those with SELECT, WHERE, or JOIN clauses.

Why is Indexing Important?

Without an index, the database has to scan every row in a table to find what it’s looking for. This process is called a "full table scan," and it can be slow, especially in large databases. Indexing speeds up this process by narrowing down the search area.

Example:

-- Creating an index on the 'email' column
CREATE INDEX idx_email ON customers (email);

In this example, an index is created on the email column in the customers table, making it quicker to search for customers by their email addresses.

2. Types of Indexes

There are several types of indexes, each with its own use case. Let’s explore the most common ones.

2.1. Clustered Index

A clustered index sorts the data rows in the table based on the index key (the column or columns used for indexing). In a table, there can only be one clustered index because the data can only be sorted in one way.

Key Points:

• Physically organizes the data in the table.
• Faster for retrieving data sorted by the indexed column.
• Each table can have only one clustered index.

Example:

-- Creating a clustered index on 'customer_id'
CREATE CLUSTERED INDEX idx_customer_id ON customers (customer_id);

2.2. Non-Clustered Index

A non-clustered index is different because it doesn’t change the physical order of the data in the table. Instead, it creates a separate structure that points to the actual data rows. A table can have multiple non-clustered indexes.

Key Points:

• Does not affect the physical order of the data.
• Allows multiple indexes on different columns.
• Slower than a clustered index but still improves performance.

Example:

-- Creating a non-clustered index on 'email'
CREATE NONCLUSTERED INDEX idx_email ON customers (email);

2.3. Unique Index

A unique index ensures that all the values in the indexed column are unique. This type of index is automatically created when you apply a UNIQUE constraint, but you can also create it manually.

Key Points:

• Ensures uniqueness of the values in the column.
• Automatically applied when using the UNIQUE constraint.

Example:

-- Creating a unique index on 'email'
CREATE UNIQUE INDEX idx_unique_email ON customers (email);

2.4. Composite Index

A composite index is an index that is created on two or more columns. It helps when queries use multiple columns in the WHERE clause.

Key Points:

• Index is created on more than one column.
• Improves performance for queries involving multiple columns.

Example:

-- Creating a composite index on 'first_name' and 'last_name'
CREATE INDEX idx_name ON customers (first_name, last_name);

3. When Should You Use Indexes?

Indexes are great for speeding up data retrieval, but they do come with some drawbacks. For example, they can slow down INSERT, UPDATE, and DELETE operations because the database has to update the index along with the data. Therefore, you should use indexes on columns that are frequently used in SELECT queries or JOIN conditions.

Conclusion

Indexing is a powerful tool that helps databases retrieve data faster by creating a roadmap for the database to follow. By using clustered, non-clustered, unique, and composite indexes, you can optimize your queries and improve overall performance. However, it’s important to use them wisely to avoid negative effects on write operations like INSERT and UPDATE.