Indexing, Hashing & Query Optimization in SQL

Indexes are essential for faster data retrieval and query optimization. In this tutorial, we’ll explore B-Tree, B+ Tree, and Hash indexes using a Students table.
Step 1 — Create Students Table and Insert Data
CREATE TABLE Students (
roll_no INT PRIMARY KEY,
name VARCHAR(50),
dept VARCHAR(30),
cgpa DECIMAL(3,2)
);
INSERT INTO Students (roll_no, name, dept, cgpa) VALUES
(101, 'Alice', 'CSBS', 8.5),
(102, 'Bob', 'CSBS', 7.8),
(103, 'Charlie', 'ECE', 8.2),
(104, 'David', 'CSBS', 9.0),
(105, 'Eva', 'ECE', 6.9),
(106, 'Frank', 'CSBS', 8.1),
(107, 'Grace', 'CSBS', 7.5),
(108, 'Hannah', 'ECE', 8.6),
(109, 'Ivy', 'CSBS', 8.0),
(110, 'Jack', 'CSBS', 8.9),
(111, 'Kiran', 'ECE', 7.7),
(112, 'Liam', 'CSBS', 8.3),
(113, 'Mia', 'ECE', 9.2),
(114, 'Noah', 'CSBS', 8.4),
(115, 'Olivia', 'ECE', 7.6),
(116, 'Paul', 'CSBS', 8.7),
(117, 'Quinn', 'CSBS', 7.9),
(118, 'Ria', 'ECE', 8.8),
(119, 'Sam', 'CSBS', 8.0),
(120, 'Tina', 'ECE', 9.1);

Screenshot:

Step 2 — Create a B-Tree Index on roll_no
B-Tree indexes are default in MySQL for numeric primary keys, but we’ll explicitly create one:

CREATE INDEX idx_roll_no ON Students(roll_no);

Query to fetch a student by roll_no = 110:

SELECT * FROM Students WHERE roll_no = 110;

Expected Output:

+---------+------+------+------+
| roll_no | name | dept | cgpa |
+---------+------+------+------+
| 110 | Jack | CSBS | 8.90 |
+---------+------+------+------+

Using a B-Tree index speeds up retrieval for single-row lookups.
screenshot:

Step 3 — Create a B+ Tree Index on cgpa

MySQL/InnoDB uses B+ Tree internally for indexes on non-primary keys.

CREATE INDEX idx_cgpa ON Students(cgpa);

Query to display all students with cgpa > 8.0:

SELECT * FROM Students WHERE cgpa > 8.0 ORDER BY cgpa DESC;

Expected Output:
+---------+--------+------+------+
| roll_no | name | dept | cgpa |
+---------+--------+------+------+
| 113 | Mia | ECE | 9.20 |
| 120 | Tina | ECE | 9.10 |
| 104 | David | CSBS | 9.00 |
| 110 | Jack | CSBS | 8.90 |
| 118 | Ria | ECE | 8.80 |
| 101 | Alice | CSBS | 8.50 |
| 114 | Noah | CSBS | 8.40 |
| 112 | Liam | CSBS | 8.30 |
| 106 | Frank | CSBS | 8.10 |
| 109 | Ivy | CSBS | 8.00 |
+---------+--------+------+------+
B+ Tree index helps with range queries like cgpa > 8.0.

screenshot:

Step 4 — Create a Hash Index on dept
Hash indexes are great for equality lookups.

Note: MySQL only supports hash indexes in Memory tables, or you can simulate using InnoDB for equality searches.

-- For demo, create a MEMORY table
CREATE TABLE StudentsHash (
roll_no INT PRIMARY KEY,
name VARCHAR(50),
dept VARCHAR(30),
cgpa DECIMAL(3,2)
) ENGINE=MEMORY;

INSERT INTO StudentsHash SELECT * FROM Students;
CREATE INDEX idx_dept_hash USING HASH ON StudentsHash(dept);

Query to fetch all students from CSBS department:

SELECT * FROM StudentsHash WHERE dept = 'CSBS';

Expected Output:

+---------+--------+------+------+
| roll_no | name | dept | cgpa |
+---------+--------+------+------+
| 101 | Alice | CSBS | 8.50 |
| 102 | Bob | CSBS | 7.80 |
| 104 | David | CSBS | 9.00 |
| 106 | Frank | CSBS | 8.10 |
| 107 | Grace | CSBS | 7.50 |
| 109 | Ivy | CSBS | 8.00 |
| 110 | Jack | CSBS | 8.90 |
| 112 | Liam | CSBS | 8.30 |
| 114 | Noah | CSBS | 8.40 |
| 116 | Paul | CSBS | 8.70 |
| 117 | Quinn | CSBS | 7.90 |
| 119 | Sam | CSBS | 8.00 |
+---------+--------+------+------+

Hash indexes optimize exact match queries, but not range queries.
screenshot:

Step 5 — Observations & Query Optimization Tips
1.B-Tree / B+ Tree: Efficient for range queries and ordered retrieval.
2.Hash Index: Fast for equality searches but not for ranges.
3.Indexes improve SELECT performance but may slow down INSERT/UPDATE due to maintenance.
4.Always choose the right index type depending on query patterns.

✅ Full SQL Script
CREATE DATABASE IF NOT EXISTS StudentsDemo;
USE StudentsDemo;

CREATE TABLE Students (
roll_no INT PRIMARY KEY,
name VARCHAR(50),
dept VARCHAR(30),
cgpa DECIMAL(3,2)
);
-- Insert 20 sample records
INSERT INTO Students (roll_no, name, dept, cgpa) VALUES
(101, 'Alice', 'CSBS', 8.5),
(102, 'Bob', 'CSBS', 7.8),
(103, 'Charlie', 'ECE', 8.2),
(104, 'David', 'CSBS', 9.0),
(105, 'Eva', 'ECE', 6.9),
(106, 'Frank', 'CSBS', 8.1),
(107, 'Grace', 'CSBS', 7.5),
(108, 'Hannah', 'ECE', 8.6),
(109, 'Ivy', 'CSBS', 8.0),
(110, 'Jack', 'CSBS', 8.9),
(111, 'Kiran', 'ECE', 7.7),
(112, 'Liam', 'CSBS', 8.3),
(113, 'Mia', 'ECE', 9.2),
(114, 'Noah', 'CSBS', 8.4),
(115, 'Olivia', 'ECE', 7.6),
(116, 'Paul', 'CSBS', 8.7),
(117, 'Quinn', 'CSBS', 7.9),
(118, 'Ria', 'ECE', 8.8),
(119, 'Sam', 'CSBS', 8.0),
(120, 'Tina', 'ECE', 9.1);
-- B-Tree index on roll_no
CREATE INDEX idx_roll_no ON Students(roll_no);
SELECT * FROM Students WHERE roll_no = 110;
-- B+ Tree index on cgpa
CREATE INDEX idx_cgpa ON Students(cgpa);
SELECT * FROM Students WHERE cgpa > 8.0 ORDER BY cgpa DESC;
-- Hash index on dept (Memory table)
CREATE TABLE StudentsHash (
roll_no INT PRIMARY KEY,
name VARCHAR(50),
dept VARCHAR(30),
cgpa DECIMAL(3,2)
) ENGINE=MEMORY;
INSERT INTO StudentsHash SELECT * FROM Students;
CREATE INDEX idx_dept_hash USING HASH ON StudentsHash(dept);
SELECT * FROM StudentsHash WHERE dept = 'CSBS';