🚀 Indexing, Hashing & Query Optimization in SQL (with Examples)

Database performance is a critical aspect of any application. Indexing, hashing, and query optimization techniques help speed up data retrieval and reduce execution time.
In this post, we’ll explore B-Tree, B+ Tree, and Hash indexing using a Students table.

📌 Step 1: Create the Students Table

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

📌 Step 2: Insert Sample Records

INSERT INTO Students (roll_no, name, dept, cgpa) VALUES
(101, 'Arun', 'CSE', 8.5),
(102, 'Priya', 'IT', 7.9),
(103, 'Ravi', 'CSBS', 8.2),
(104, 'Sneha', 'ECE', 9.1),
(105, 'Karthik', 'EEE', 6.8),
(106, 'Divya', 'CSE', 7.5),
(107, 'Vikram', 'CSBS', 8.8),
(108, 'Meena', 'IT', 9.0),
(109, 'Hari', 'CSE', 7.2),
(110, 'Nisha', 'CSBS', 8.6),
(111, 'Suresh', 'ECE', 7.4),
(112, 'Lavanya', 'IT', 8.9),
(113, 'Manoj', 'EEE', 6.5),
(114, 'Geetha', 'CSE', 8.1),
(115, 'Ramesh', 'CSBS', 7.8),
(116, 'Anitha', 'IT', 8.4),
(117, 'Saravanan', 'ECE', 9.2),
(118, 'Preethi', 'CSE', 8.7),
(119, 'Ajay', 'CSBS', 7.3),
(120, 'Deepa', 'EEE', 8.0);

📌 Step 3: Create a B-Tree Index on roll_no

CREATE INDEX idx_name ON Students(name);

📌 Step 4: Query with B-Tree Index

SELECT * FROM Students WHERE roll_no = 110;

📌 Step 5: Create a B+ Tree Index on cgpa

CREATE INDEX idx_cgpa ON Students(cgpa);

📌 Step 6: Query with B+ Tree Index

SELECT * FROM Students WHERE cgpa > 8.0;

📌 Step 7: Create a Hash Index on dept

CREATE INDEX idx_dept ON Students(dept) USING HASH;

📌 Step 8: Query with Hash Index

SELECT * FROM Students WHERE dept = 'CSBS';