📘 Introduction
In databases, indexing and hashing are techniques used to improve the speed of data retrieval operations. Without indexes, the database must scan every row to find a match, which is slow for large tables.
B-Tree Index: Used for range and equality queries (e.g., searching roll numbers or CGPAs).
B+ Tree Index: A variation of B-Tree that stores data only in leaf nodes, providing faster sequential access.
Hash Index: Used for exact match lookups (e.g., searching by department name).
Query Optimization ensures that the database uses the most efficient way to execute SQL queries — often by choosing the right index.
Step 1: Create Table
CREATE TABLE Students (
roll_no INT PRIMARY KEY,
name VARCHAR(50),
dept VARCHAR(10),
cgpa DECIMAL(3,2)
);
Step 2: Insert 20 Sample Records
Step 3: Create B-Tree Index on
EXPLAIN SELECT * FROM Students WHERE roll_no = 110;
Step 4: Fetch details of student with roll_no = 110
EXPLAIN SELECT * FROM Students WHERE roll_no = 110;
✅ The EXPLAIN keyword shows how the query uses the index
Step 5: Create B+ Tree Index on cgpa
In MySQL, normal indexes use B+ Tree structure internally.
So the syntax is the same:
CREATE INDEX idx_cgpa ON Students(cgpa);
Then query:
EXPLAIN SELECT * FROM Students WHERE cgpa > 8.0;
Step 6: Create Hash Index on dept
⚠️ Note: MySQL supports Hash Index only on MEMORY (HEAP) tables, or in PostgreSQL using USING HASH.
✅ For MySQL:
CREATE INDEX idx_dept USING HASH ON Students(dept);
✅ For PostgreSQL:
CREATE INDEX idx_dept_hash ON Students USING HASH (dept);
Step 7: Retrieve students from ‘CSBS’ department
EXPLAIN SELECT * FROM Students WHERE dept = 'CSBS';
💡 Introduction
Explain indexing, hashing, and optimization (use the intro above).
🧱 Table Creation and Data Insertion
(Include screenshot of all students.)
🌳 B-Tree Index on Roll Number
(Include query + EXPLAIN screenshot.)
🌲 B+ Tree Index on CGPA
(Show query + EXPLAIN output for cgpa > 8.0.)
🧩 Hash Index on Department
(Show query + EXPLAIN output for ‘CSBS’ department.)
⚙️ Query Optimization
Mention how indexes reduce the time for searching records.
🏁 Conclusion
Indexes and hashing make data retrieval much faster and efficient by avoiding full table scans.