What is Database Indexing?

Introduction

Database indexing is a process of creating a data structure that improves the speed of data retrieval operations on a database table. In SQL, we can create indexes on columns of a table to improve the performance of database queries.

The process of creating an index involves scanning the table and creating a new data structure that contains a copy of the indexed columns. The index is then organized in a way that allows for faster searching and sorting based on the values in those columns.

Example

Let's take an example of a database for an online store that sells products. We want to create an index on the product name column to improve the speed of searches for products by name.

First, we create a Product table to store information about the products:

CREATE TABLE Product (
    product_id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    category_id INT NOT NULL,
    supplier_id INT NOT NULL
);

Here, we have defined a Product table that has columns for the product ID, name, price, category ID, and supplier ID.

To create an index on the name column, we use the following SQL statement:

CREATE INDEX product_name_index ON Product(name);

Here, we have created an index called product_name_index on the name column of the Product table.

Now, when we search for products by name, the database engine will use the index to quickly find the rows that match the search criteria, instead of scanning the entire table.

For example, to search for products with the name 'Apple iPhone', we can use the following SQL query:

SELECT * FROM Product WHERE name = 'Apple iPhone';

With the index in place, this query will be much faster than if we had to scan the entire Product table to find the matching rows.

By using database indexing, we can significantly improve the performance of database queries, especially on large tables. However, it is important to use indexing judiciously, as creating too many indexes can slow down write operations on the table.

Types of Database Indexes (Major)

1. B-Tree Indexes: B-Tree indexes are the most common type of index used in databases. They are based on a balanced tree structure where each node contains multiple keys and pointers to child nodes. B-Tree indexes are typically used for range queries and equality searches.

2. Hash Indexes: Hash indexes are based on a hash table, which allows for very fast lookups based on a key value. However, they are not as versatile as B-Tree indexes and are typically only used for equality searches.

3. Bitmap Indexes: Bitmap indexes are a specialized type of index that are used for columns with a small number of distinct values. They use a bitmap to represent the presence or absence of each value, which allows for very fast bitwise operations.

Summary

In summary, database indexing is a powerful technique for improving the performance of database queries. By creating an index on one or more columns of a table, the database engine can quickly locate and retrieve the rows that match a query's filter conditions. This can greatly reduce query execution time and improve overall database performance.