Indexing, Hashing & Query Optimization in SQL — A Practical Guide

Introduction

When databases grow large, query performance becomes critical.
Indexes — like B-Trees, B+ Trees, and Hash indexes — help speed up data retrieval by reducing the number of rows scanned.

In this tutorial, we’ll:

Create a Students table.

Insert 20 records.

Create B-Tree, B+ Tree, and Hash indexes.

Run optimized queries and observe performance differences.

Let’s dive in! ⚙️

🏗️ Step 1: Create the Table

We’ll start by creating a simple Students table.

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

✅ Explanation:

roll_no uniquely identifies each student.

cgpa helps demonstrate range-based queries.

dept is great for hash indexing.

📥 Step 2: Insert 20 Sample Records

Let’s insert some demo data.

INSERT INTO Students (roll_no, name, dept, cgpa) VALUES
(101, 'Arun Kumar', 'CSE', 8.7),
(102, 'Meena Devi', 'ECE', 7.9),
(103, 'Karthik Raja', 'CSBS', 8.2),
(104, 'Priya Mohan', 'IT', 9.0),
(105, 'Vishal', 'CSBS', 8.1),
(106, 'Ananya', 'MECH', 7.5),
(107, 'Ravi', 'EEE', 6.9),
(108, 'Swetha', 'CSE', 9.1),
(109, 'Balaji', 'IT', 8.3),
(110, 'Hari', 'CSBS', 8.9),
(111, 'Nisha', 'ECE', 7.4),
(112, 'Sanjay', 'MECH', 8.0),
(113, 'Keerthi', 'CSE', 9.3),
(114, 'Deepak', 'CSBS', 7.8),
(115, 'Gayathri', 'EEE', 8.4),
(116, 'Suresh', 'IT', 7.6),
(117, 'Lavanya', 'CSE', 8.8),
(118, 'Kavin', 'CSBS', 9.2),
(119, 'Rohit', 'MECH', 8.1),
(120, 'Preethi', 'ECE', 8.0);

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

✅ Explanation:

B-Tree indexes are default in most SQL databases.

They help speed up exact match or range queries on numeric columns.

🔍 Query: Fetch student with roll_no = 110
SELECT * FROM Students WHERE roll_no = 110;

You’ll notice the query runs instantly, thanks to the index.

🌳 Step 4: Create a B+ Tree Index on cgpa

(In some databases, B+ Trees are the underlying structure of B-Tree indexes. You can simulate it using a normal index.)

CREATE INDEX idx_cgpa_bplustree ON Students (cgpa);

✅ Use Case:
Efficient for range queries, such as finding all students with cgpa > 8.0.

🧮 Query: List Students with cgpa > 8.0
SELECT * FROM Students WHERE cgpa > 8.0;

The B+ Tree helps in retrieving a range of values quickly.
You can check query performance with:

EXPLAIN SELECT * FROM Students WHERE cgpa > 8.0;

⚡ Step 5: Create a Hash Index on dept
CREATE INDEX idx_dept_hash ON Students USING HASH (dept);

✅ Explanation:

Hash indexes are best for equality comparisons (e.g., dept = 'CSBS').

Not suitable for range or sorting operations.

🧭 Query: Retrieve All Students from the 'CSBS' Department
SELECT * FROM Students WHERE dept = 'CSBS';

You’ll see results appear very fast

🧩 Step 6: Query Optimization Tips

Use indexes on columns frequently used in WHERE, JOIN, or ORDER BY clauses.

Avoid indexing columns with low uniqueness (like gender or status).

Periodically run ANALYZE or VACUUM to update statistics.

Use EXPLAIN to see how your query executes.

🏁 Conclusion

By combining B-Tree, B+ Tree, and Hash Indexes, we optimized queries efficiently:

B-Tree: Fast for primary key lookups.

B+ Tree: Great for range queries.

Hash Index: Perfect for equality conditions.

With these techniques, your database queries can run 10x faster 🚀