Indexing, Hashing & Query Optimization in SQL

Intro

When working with large databases, retrieving data efficiently becomes a big challenge. SQL provides indexing and hashing techniques to speed up query execution. In this blog, we’ll explore B-Tree, B+ Tree, and Hash indexes with hands-on SQL examples using a Students table.

Create the Students Table

CREATE TABLE Students (
    roll_no INT PRIMARY KEY,
    name VARCHAR(50),
    dept VARCHAR(20),
    cgpa DECIMAL(3,2)
);

Insert Sample Records

INSERT INTO Students VALUES
(101, 'Aarav', 'CSBS', 8.5),
(102, 'Meera', 'ECE', 7.2),
(103, 'Rohan', 'MECH', 6.9),
(104, 'Sita', 'CIVIL', 8.1),
(105, 'Vikram', 'CSE', 9.0),
(106, 'Priya', 'IT', 8.3),
(107, 'Arjun', 'CSBS', 7.5),
(108, 'Neha', 'ECE', 8.7),
(109, 'Kiran', 'CSE', 6.8),
(110, 'Rahul', 'CSBS', 9.2),
(111, 'Sneha', 'MECH', 7.9),
(112, 'Dev', 'CIVIL', 8.4),
(113, 'Pooja', 'CSE', 7.3),
(114, 'Varun', 'IT', 8.6),
(115, 'Isha', 'ECE', 9.1),
(116, 'Nikhil', 'MECH', 7.6),
(117, 'Ritu', 'CIVIL', 6.5),
(118, 'Sameer', 'CSE', 8.8),
(119, 'Tina', 'CSBS', 7.8),
(120, 'Yash', 'IT', 9.3);

Now we have 20 student records.

Create a B-Tree Index on roll_no

B-Tree indexes are default in most RDBMS. They make searching on primary/unique columns faster.

CREATE INDEX idx_rollno_btree ON Students(roll_no);

The DBMS uses the B-Tree index to find roll_no = 110 quickly without scanning the whole table.

Create a B+ Tree Index on cgpa

B+ Trees are widely used for range queries.

CREATE INDEX idx_cgpa_bplustree ON Students(cgpa);

Instead of checking each row, the B+ Tree index helps to traverse efficiently for CGPA values greater than 8.5 .

Create a Hash Index on dept

Hash indexes are best for equality lookups (e.g., = condition).

CREATE INDEX idx_dept_hash ON Students(dept) USING HASH;

Query Optimization

• Without indexes → Full Table Scan (slow for large datasets).
• With indexes → Optimized Execution Plan (quick lookup).
• Use EXPLAIN to check whether your query uses the index.

Final Thoughts

• B-Tree Index → Best for unique lookups & ordering.
• B+ Tree Index → Best for range queries.
• Hash Index → Best for equality lookups.

By using indexes wisely, we can reduce query execution time drastically in real-world applications.