What is indexing in DB?

What is Index?

Indexes are database structures that improve the performance of search queries. They work like a phone book index, where you can quickly look up a person's phone number without having to go through every entry in the book.

In a similar way, a database index helps to speed up the search for data by creating a sorted reference to the values stored in one or more columns of a table. This makes it faster for the database to retrieve data, which can be especially important for large tables or complex queries.

So, Whenever a query is executed, the database engine searches the index for the keyword and retrieves the corresponding rows from the table. This process is much faster than scanning the entire table to find the matching rows.

Drawbacks of Indexes

Indexes can significantly improve query performance, but they also have some drawbacks. Creating and maintaining indexes takes time and resources, and they can consume a significant amount of storage space. Additionally, indexes can actually slow down write operations because they need to be updated every time a row is inserted, updated, or deleted.

When to use indexes?

Here are some examples when you might want to use an index:

1. Primary keys: When you create a table, you can specify one or more columns as the primary key. This creates a unique index on those columns, which is used to enforce the primary key constraint and ensure that each row has a unique identifier.

2. Foreign keys: When you create a foreign key constraint, the database engine automatically creates an index on the foreign key column(s) in the referencing table. This speeds up queries that join the referencing and referenced tables.

3. Frequently searched columns: If you have a table with a large number of rows and one or more columns that are frequently searched, you can create an index on those columns to speed up those queries.

4. Order by and group by clauses: If your queries frequently include an order by or group by clause, you can create an index on the column(s) being sorted or grouped.

5. Where clauses: If your queries frequently include a where clause that filters the results based on a specific value or range of values, you can create an index on the column(s) being filtered.

It's important to note that not all columns need an index. In fact, too many indexes can actually slow down performance because the database engine has to spend more time maintaining them. It's best to create indexes selectively based on the needs of your queries.