What are indexes in the database?

Jul 20, 2020 By Puneet Verma

Indexing is a data structure that allows the fast retrieval of data from the database.
Indexing of database is similar to the index of the book.

How it fastens the retrievals
The database maintains the B+ tree for the indexed column because of this searching is very efficient.

Let's see it with an example.

select * form table_name where col_1=140385;

In this query, MySQL server will conduct a full table scan, i.e. all row present in that table. If we index on that col_1, then MySql server will retrieve the data in one shot.

You can use Explain keyword to verify this.

How to create an index
Use below query to create an index.

create index index_name ON table_name (col_1);


Where to use?
In the application where search/retrieval is more than write operations, then go for indexing.

Notes.

  • Inserting, deletion is not efficient because for every insertion we may have to restructure the tree B+ Tree.
  • Updation is efficient if we are updating non-indexed columns
  • It required more storage to maintain additional structures.
  • We can have more than one columns as an index.
  • We should index the column which is often searched.
  • Use indexing wisely, using too many indexes may degrade the performed.