This content originally appeared on DEV Community and was authored by Prabavathy Balagurusamy
🎯 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:
🧠 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 🚀
This content originally appeared on DEV Community and was authored by Prabavathy Balagurusamy
Prabavathy Balagurusamy | Sciencx (2025-10-06T14:53:43+00:00) Optimizing SQL Performance with Indexing, Hashing, and B+ Trees. Retrieved from https://www.scien.cx/2025/10/06/optimizing-sql-performance-with-indexing-hashing-and-b-trees/
Please log in to upload a file.
There are no updates yet.
Click the Upload button above to add an update.