Indexing, Hashing & Query Optimization

Indexing, Hashing & Query Optimization in SQL

When working with large datasets, searching for data efficiently is crucial. Indexing and Hashing help databases retrieve data faster, improving performance for queries.

In this post, we’ll demonstrate B-Tree, B+ Tree, and Hash indexes using a Students table.

Create Database and Students Table
CREATE DATABASE college;
USE college;

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, 'Alice', 'CSBS', 9.1),
(102, 'Bob', 'ECE', 7.5),
(103, 'Charlie', 'MECH', 8.2),
(104, 'David', 'CIVIL', 6.9),
(105, 'Emma', 'CSBS', 8.7),
(106, 'Frank', 'IT', 7.8),
(107, 'Grace', 'ECE', 8.5),
(108, 'Helen', 'MECH', 7.2),
(109, 'Ivy', 'CIVIL', 8.9),
(110, 'Jack', 'CSBS', 9.3),
(111, 'Karan', 'IT', 6.8),
(112, 'Leo', 'ECE', 8.0),
(113, 'Mia', 'MECH', 7.9),
(114, 'Nina', 'CSBS', 9.0),
(115, 'Owen', 'CIVIL', 8.3),
(116, 'Paul', 'IT', 7.4),
(117, 'Queen', 'ECE', 8.6),
(118, 'Ravi', 'CSBS', 8.8),
(119, 'Sophia', 'MECH', 7.6),
(120, 'Tom', 'CIVIL', 8.1);

B-Tree Index on roll_no
CREATE INDEX idx_rollno_btree
ON Students(roll_no);

SELECT * FROM Students
WHERE roll_no = 110;

B-Tree indexes are ideal for equality and range queries.

B+ Tree Index on cgpa
CREATE INDEX idx_cgpa_bplustree
ON Students(cgpa);

SELECT * FROM Students
WHERE cgpa > 8.0;

✔ B+ Tree indexes are optimized for range queries, making it fast to find all students with CGPA above 8.0.

Hash Index on dept
CREATE INDEX idx_dept_hash
ON Students(dept) USING HASH;

SELECT * FROM Students
WHERE dept = 'CSBS';

Hash indexes are perfect for equality lookups, such as fetching all students from a specific department.

Summary

B-Tree Index (roll_no) → Fast equality and range queries.

B+ Tree Index (cgpa) → Efficient for range queries (cgpa > 8.0).

Hash Index (dept) → Best for exact-match queries (dept = 'CSBS').

Indexes improve query performance significantly, especially when datasets are large. Using the right type of index based on your query patterns is key to database optimization.