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 !!!
Top comments (0)