top of page
Programming

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 -

  1. What is Database Indexing?

  2. Data Structures that Database Index use

  3. B-TREE Index

  4. B+ TREE Index

  5. Hash Index

  6. Downloading sample database for experiments

  7. Diving into the database index parameters

  8. Performance testing with database index

  9. How to create a database index?

  10. The Right Way to Add Database Index

  11. Types of Database Indexes

  12. Single Level Indexing

  13. Ordered Indexing

  14. Multi-level Indexing

  15. Conclusion: Your Data is Now Ready for Fast Queries!

  16. Frequently Asked Questions (FAQs)


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 -

  1. Facilitate the ability to fetch data faster and more efficiently

  2. 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.