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

This content originally appeared on DEV Community and was authored by Saifulhaq S

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';

This content originally appeared on DEV Community and was authored by Saifulhaq S

Print Share Comment Cite Upload Translate Updates

APA

Saifulhaq S | Sciencx (2025-10-06T09:38:00+00:00) 🚀 Indexing, Hashing & Query Optimization in SQL (with Examples). Retrieved from https://www.scien.cx/2025/10/06/%f0%9f%9a%80-indexing-hashing-query-optimization-in-sql-with-examples/

MLA

" » 🚀 Indexing, Hashing & Query Optimization in SQL (with Examples)." Saifulhaq S | Sciencx - Monday October 6, 2025, https://www.scien.cx/2025/10/06/%f0%9f%9a%80-indexing-hashing-query-optimization-in-sql-with-examples/

HARVARD

Saifulhaq S | Sciencx Monday October 6, 2025 » 🚀 Indexing, Hashing & Query Optimization in SQL (with Examples)., viewed ,<https://www.scien.cx/2025/10/06/%f0%9f%9a%80-indexing-hashing-query-optimization-in-sql-with-examples/>

VANCOUVER

Saifulhaq S | Sciencx - » 🚀 Indexing, Hashing & Query Optimization in SQL (with Examples). [Internet]. [Accessed ]. Available from: https://www.scien.cx/2025/10/06/%f0%9f%9a%80-indexing-hashing-query-optimization-in-sql-with-examples/

CHICAGO

" » 🚀 Indexing, Hashing & Query Optimization in SQL (with Examples)." Saifulhaq S | Sciencx - Accessed . https://www.scien.cx/2025/10/06/%f0%9f%9a%80-indexing-hashing-query-optimization-in-sql-with-examples/

IEEE

" » 🚀 Indexing, Hashing & Query Optimization in SQL (with Examples)." Saifulhaq S | Sciencx [Online]. Available: https://www.scien.cx/2025/10/06/%f0%9f%9a%80-indexing-hashing-query-optimization-in-sql-with-examples/. [Accessed: ]

rf:citation

» 🚀 Indexing, Hashing & Query Optimization in SQL (with Examples) | Saifulhaq S | Sciencx | https://www.scien.cx/2025/10/06/%f0%9f%9a%80-indexing-hashing-query-optimization-in-sql-with-examples/ |

Please log in to upload a file.

There are no updates yet.
Click the Upload button above to add an update.

You must be logged in to translate posts. Please log in or register.