Optimizing SQL Performance with Indexing, Hashing, and B+ Trees

🎯 Overview

In this blog, we’ll explore Indexing, Hashing, and Query Optimization concepts practically using SQL.
We’ll:

• Create a Students table
• Insert sample records
• Create B-Tree, B+ Tree, and Hash indexes
• Run queries and see how indexing improves performance

🧩 Step 1: Create the Students Table

Let’s start by creating a simple table with columns — roll_no, name, dept, and cgpa.

CREATE TABLE Students (
roll_no INT PRIMARY KEY,
name VARCHAR(50),
dept VARCHAR(10),
cgpa FLOAT
);

🧾 Step 2: Insert 20 Sample Records

Here’s some sample data to work with:

INSERT INTO Students (roll_no, name, dept, cgpa) VALUES
(101, 'Ananya', 'CSBS', 9.1),
(102, 'Rahul', 'CSE', 8.3),
(103, 'Divya', 'ECE', 7.9),
(104, 'Karthik', 'MECH', 8.5),
(105, 'Sanjay', 'EEE', 7.4),
(106, 'Priya', 'CSBS', 9.3),
(107, 'Deepak', 'CSE', 8.0),
(108, 'Meena', 'IT', 8.7),
(109, 'Vishal', 'CSE', 9.0),
(110, 'Kavya', 'CSBS', 8.8),
(111, 'Harini', 'ECE', 7.2),
(112, 'Rakesh', 'MECH', 8.1),
(113, 'Aishwarya', 'EEE', 9.2),
(114, 'Manoj', 'IT', 8.4),
(115, 'Siva', 'CSBS', 9.5),
(116, 'Sneha', 'CSE', 7.8),
(117, 'Nithin', 'MECH', 8.9),
(118, 'Gayathri', 'CSBS', 8.2),
(119, 'Arun', 'ECE', 9.0),
(120, 'Monika', 'IT', 7.5);

🌳 Step 3: Create a B-Tree Index on roll_no

Most RDBMSs (like MySQL and PostgreSQL) use B-Trees by default for indexing primary keys and unique columns.

CREATE INDEX idx_rollno_btree ON Students(roll_no);

✅ Why B-Tree?
It helps quickly locate records in sorted order. The database doesn’t need to scan every row — it navigates the index like a tree structure.

🔍 Step 4: Query with B-Tree Index

Let’s fetch details of the student with roll_no = 110:

SELECT * FROM Students WHERE roll_no = 110;

The query optimizer will automatically use the B-Tree index to quickly find the matching record.

Output:

🌲 Step 5: Create a B+ Tree Index on cgpa

In databases, B+ Trees are often used for range queries, especially in systems like PostgreSQL.

CREATE INDEX idx_cgpa_bplustree ON Students(cgpa);

✅ Why B+ Tree?
It stores all values in leaf nodes in sorted order, perfect for range queries like >, <, BETWEEN, etc.

📈 Step 6: Query Using B+ Tree

Now, display all students with CGPA greater than 8.0:

SELECT * FROM Students WHERE cgpa > 8.0;

Output:

💡 Query Optimization:
The database engine uses the B+ Tree index to skip lower CGPA values, scanning only relevant entries.

🧮 Step 7: Create a Hash Index on dept

Hash indexes are ideal for equality comparisons (=), such as finding all students from a specific department.

CREATE INDEX idx_dept_hash ON Students USING HASH (dept);

✅ Why Hash Index?
Hashing maps keys directly to fixed locations — it’s extremely fast for equality lookups.

🧠 Step 8: Query Using Hash Index

Retrieve all students from the CSBS department:

SELECT * FROM Students WHERE dept = 'CSBS';

Output:

Step 9: Test Query Performance

output:

🧠 Final Thoughts

Indexing and hashing are powerful optimization tools that can drastically improve database performance.
Choosing the right index type based on the query type — equality, range, or sorting — ensures your database runs efficiently even with millions of records.

💬 Conclusion

Optimized queries = Faster apps! ⚡
Index smartly, hash wisely, and watch your queries fly 🚀