When working with databases, query performance is everything. Indexing and hashing help speed up data retrieval dramatically, especially when working with large tables.
In this blog, we’ll explore:
B-Tree Indexing
B+ Tree Indexing (conceptually, in practice it’s B-Tree)
Hash Indexing
Query optimization examples
We’ll use a sample Students table with 20+ records for demonstration.
📌 Step 1: Create the Students Table
CREATE TABLE Students (
roll_no INT PRIMARY KEY,
name VARCHAR(50),
dept VARCHAR(10),
cgpa DECIMAL(3,2)
);
📌 Step 2: Insert Sample Records
INSERT INTO Students (roll_no, name, dept, cgpa) VALUES
(101, 'Aarav', 'CSE', 8.5),
(102, 'Diya', 'ECE', 7.9),
(103, 'Rahul', 'MECH', 6.8),
(104, 'Sneha', 'CSE', 8.9),
(105, 'Karthik', 'EEE', 7.2),
(106, 'Meera', 'CSBS', 9.1),
(107, 'Arjun', 'CSE', 8.0),
(108, 'Nisha', 'IT', 7.5),
(109, 'Vikram', 'CSBS', 8.7),
(110, 'Priya', 'ECE', 9.2),
(111, 'Varun', 'MECH', 7.0),
(112, 'Pooja', 'CSE', 8.6),
(113, 'Rohit', 'EEE', 6.9),
(114, 'Aditi', 'CSBS', 8.3),
(115, 'Suresh', 'IT', 7.8),
(116, 'Ishita', 'CSE', 9.0),
(117, 'Manoj', 'ECE', 6.7),
(118, 'Ananya', 'CSBS', 8.4),
(119, 'Deepak', 'MECH', 7.1),
(120, 'Krishna', 'EEE', 8.2);
✅ We now have enough data to work with indexing.
📌 Step 3: B-Tree Index on roll_no
B-Trees are the default index type in most RDBMS (MySQL, PostgreSQL). They are great for exact lookups on primary key columns.
CREATE INDEX idx_rollno ON Students (roll_no);
Query with Index:
SELECT *
FROM Students
WHERE roll_no = 110;
👉 This efficiently returns all students with CGPA greater than 8.0.
📌 Step 4: B+ Tree Index on cgpa
In practice, B+ Trees are also implemented as B-Trees in MySQL/PostgreSQL. They are excellent for range queries.
CREATE INDEX idx_cgpa ON Students (cgpa);
Query with Index:
SELECT *
FROM Students
WHERE cgpa > 8.0;
📌 Step 5: Hash Index on dept
Hash indexes are best for equality-based queries (e.g., dept = 'CSBS').
PostgreSQL
CREATE INDEX idx_dept ON Students USING HASH (dept);
Query with Index:
SELECT *
FROM Students
WHERE dept = 'CSBS';
👉 This fetches all students in the CSBS department (Meera, Vikram, Aditi, Ananya).
📊 Final Thoughts
B-Tree Index → Best for unique lookups (like roll numbers).
B+ Tree Index → Great for range queries (cgpa > 8).
Hash Index → Ideal for equality checks (dept = 'CSBS').
By applying the right indexing strategy, you can optimize query performance significantly.
Top comments (0)