When working with large databases, query performance becomes critical. Retrieving data without optimization can be slow. This is where indexes come into play.

In this blog, we will explore:

B-Tree Index for primary key lookups

B+ Tree Index for range queries

Hash Index for equality searches

We will use a Students table with sample data and see how different indexes improve query performance. CREATE TABLE Students ( roll_no INT PRIMARY KEY, name VARCHAR(50), dept VARCHAR(10), cgpa DECIMAL(3,2) );

INSERT INTO Students (roll_no, name, dept, cgpa) VALUES (101, ‘Alice’, ‘CSBS’, 8.5), (102, ‘Bob’, ‘ECEN’, 7.9), ... (120, ‘Tom’, ‘ECEN’, 7.4);

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;

Hash Index on dept CREATE INDEX idx_dept_hash ON Students(dept); SELECT * FROM Students WHERE dept = ‘CSBS’;

Conclusion

In this blog, we:

Created a Students table with sample records

Applied B-Tree, B+ Tree, and Hash indexes

Ran queries to demonstrate performance improvement Thank you @santhoshnc sir !!!