Mastering B-Tree, B+Tree, and Hash Indexes

Ever wondered why some SQL queries take milliseconds, while others take minutes?
The secret lies in a powerful data structure Indexes!

In this tutorial, we’ll explore how to create and use B-Tree, B+Tree, and Hash indexes using a simple Students table example.
Let’s dive right in!

🎯 What Are Indexes in Databases?

Think of an index as a shortcut just like the index in a book.
Instead of flipping through every page, the database can jump directly to the data you want.

Step 1: Create the Students Table

Step 2: Insert 20 Sample Records

Let’s fill our table with student data.

Step 3: Create a B-Tree Index on roll_no
CREATE INDEX idx_rollno ON Students(roll_no);

Step 4: Query Using B-Tree Index

SELECT * FROM Students WHERE roll_no = 110;

Step 5: Create a B+ Tree Index on cgpa

SELECT * FROM Students WHERE cgpa > 8.0;

Step 6: Query Using Hash Index

SELECT * FROM Students WHERE dept = 'CSBS';

Conclusion

We just built a mini high-performance database system from scratch!
Using B-Tree, B+Tree, and Hash indexes, you’ve learned how databases actually speed up query execution behind the scenes.