Indexes When to Use and When to Avoid In SQL.

Improve the performance of your database by knowing the concept of indexes.

Indexing

Through this article, we’ll learn what indexes are, why to use them, how to create them, and when to avoid them. The concept of indexes is mostly asked in the interviews which are focused on SQL.

What Is an Index?

Indexes are a special kind of lookup table that is used by the database search engine to speed up data retrieval from the tables. Basically, an index is a pointer that points to the tuples of a table.

An index in a database is very similar to an index in the back of a book. It means when we want to jump to a specific topic from a book then we can first see the index of the book and then find the page number for that topic and then open that topic by going to that page number.

Another example can be, imagine we are looking to find a piece of information that is within a large table in the database. To get this information out of the table from the database, the search engine will look through every record until it finds it assuming the records of the table are unsorted. If the data we are looking for is towards the very end, this query would take a long time to run.

For such scenarios, indexes are very useful to query the tables and get the results very quickly. If the records of the table would have been sorted then it would have been easier to search the record.

sorted records on name

In the above example, records of the table are sorted on the basis of id’s, and to search for a name in the table we have to iterate through all the records of the table for finding a match. This would take a linear time that is O(n). Hence we can have an index on the name which is sorted alphabetically and points to actual records of the table. Using this index searching for a name would take log(n) time as the records are alphabetically sorted and a binary search can be performed.

Why Indexes Are Used?

Indexes help faster data retrieval from the databases. Basically, it speeds up the select queries and where clause. But at the same time, it degraded the performance of INSERT and UPDATE queries. Hence, we should be careful while using the indexes.

How To Create Indexes?

CREATE INDEX Syntax:

The below syntax creates an index (index_name) on a table. This index will allow duplicate values.

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

CREATE UNIQUE INDEX Syntax:

The below syntax creates a unique index (index_name) on a table. This index will not allow duplicate values.

CREATE UNIQUE INDEX index_name
ON table_name (column1, column2, …);

Note: The syntax for creating indexes varies among different databases. So check the syntax for creating indexes in your database.

DROP INDEX Syntax:

The DROP INDEX statement is used to delete an index in a table.

MySQL:

ALTER TABLE table_name
DROP INDEX index_name;

MS Access:

DROP INDEX index_name ON table_name;

SQL Server:

DROP INDEX table_name.index_name;

DB2/Oracle:

DROP INDEX index_name;

When to Create Single-column and Composite Indexes?

Whether to create a single-column index or a composite index, depends on the columns being used in SELECT statements for filtering rows of a table.

We should look at the column(s) that are used very frequently in the query’s WHERE clause as filter conditions.

If the WHERE clause uses a single column for filtering the rows then a single-column index is good otherwise if multiple columns are used for filtering then a composite index is suitable.

What Is the Implicit Index?

Implicit indexes are indexes automatically created by the database server when a table is created. Indexes are automatically created for the primary key and unique constraints.

When Should We Avoid Using Indexes?

The purpose of indexes is to enhance a database’s performance, there are multiple scenarios where indexes should not be used.

1. Indexes should not be used on tables containing few records.
2. Tables that have frequent, large batch updates or insert operations.
3. Indexes should not be used on columns that contain a high number of NULL values.
4. Indexes should not be used on the columns that are frequently manipulated.

Remember creating too many indexes on a table can slow down your index-related operations to multi-fold.

That’s all for this article. Hope you enjoyed it !!!

Follow Vikram Gupta for Similar Content.

You may like to read similar SQL Interview Questions.

1. Know the Difference between TRUNCATE, DELETE, and DROP

Know the Difference between TRUNCATE, DELETE, and DROP

2. Group by and Having Clauses in SQL

Group by and Having Clauses in SQL

3. Views in SQL — Create, Update, and Drop

Views in SQL — Create, Update, and Drop

4. Indexes When to Use and When to Avoid In SQL.

Indexes When to Use and When to Avoid In SQL.