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 …
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 !!!