How Database Indexing Makes Your Query Faster in a Relational Database - The Complete Guide
Updated: Dec 9, 2022
Imagine you are in a library that has thousands of books and you have a book titled "Find me". The problem is that all the books are arranged based on their serial number. You would have to go through each book serially and check their name until you find the book you are searching for. You would have to spend a couple of hours to find that book. Now imagine that the librarian gives you a list of all the books sorted alphabetically and the corresponding serial number of the book. How long do you think it will take you to find that book? A couple of minutes, right?
With the list that the librarian gave you, your search time was reduced from hours to minutes. Now think of this library as a table in a database and then this list would be the database index.
If you are a software developer, a database engineer, a system architect, or a computer science student, you would need to design and develop your system such that it is efficient and optimized. If you don't know how database indexing works and want to know more about it and the good practices that need to be followed while creating new indexes, this blog is for you.
Let's dive right in -
What is Database Indexing?
A database index is a data structure that improves the performance of database queries by making them faster. The database index makes the data easier to retrieve and speeds up data access. This entire process is called database indexing. These database indexes are actually data structures that store the search keys in a way that they can be easily searched.
Databases like MySQL and PostgreSQL also have a system of caches and parallel searches to make querying faster and more optimized.
Data Structures that Database Index use
B-TREE and HASH are the 2 most common database indices that are used. Let's look at them.
B-TREE Index
B-TREE is the most popular and default index type in most relational database management systems. The `B` in 'B-TREE` stands for (Balanced).
There are mainly 2 goals that this index data structure takes care of -
Facilitate the ability to fetch data faster and more efficiently
Store the data in a sorted manner
The B-TREE index makes use of the tree data structure to sort the data and allow querying faster. You may be aware of how the binary search tree works. The search operation in a B-TREE is similar to what happens in a balanced binary search tree but with few differences.
Each node in a B-TREE has a range of values.