Indexing, Hashing & Query Optimization

🎯 Objective

To understand how to improve query performance using B-Tree, B+ Tree, and Hash indexing in MySQL.

---

🧩 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, 'Alice', 'CSBS', 8.7),
(102, 'Rahul', 'ECE', 7.9),
(103, 'Priya', 'IT', 8.1),
(104, 'Kavin', 'CSBS', 9.0),
(105, 'Meena', 'EEE', 7.5),
(106, 'Deepak', 'CIVIL', 8.0),
(107, 'Sathya', 'MECH', 6.9),
(108, 'Ravi', 'IT', 7.8),
(109, 'Sneha', 'CSBS', 8.9),
(110, 'Manoj', 'ECE', 8.3),
(111, 'Divya', 'CSBS', 9.1),
(112, 'Hari', 'MECH', 7.4),
(113, 'Nithya', 'EEE', 7.8),
(114, 'Karthik', 'CIVIL', 8.2),
(115, 'Anu', 'CSBS', 8.4),
(116, 'Gokul', 'IT', 7.7),
(117, 'Vishnu', 'ECE', 8.0),
(118, 'Saran', 'EEE', 8.5),
(119, 'Preethi', 'CIVIL', 7.6),
(120, 'Varun', 'CSBS', 9.3);

🌳 Step 3: Create a B-Tree Index on roll_no

CREATE INDEX idx_roll_no ON Students(roll_no);

✅ Query:

SELECT * FROM Students WHERE roll_no = 110;

💡 Explanation:

• B-Tree indexes help in range-based and sorted searches efficiently.
• The lookup time reduces from O(n) to O(log n).

🌿 Step 4: Create a B+ Tree Index on cgpa

CREATE INDEX idx_cgpa ON Students(cgpa);

✅ Query:

SELECT * FROM Students WHERE cgpa > 8.0;

⚙️ Step 5: Create a Hash Index on dept

CREATE INDEX idx_dept_hash USING HASH ON Students(dept);

✅ Query:

SELECT * FROM Students WHERE dept = 'CSBS';

💡 Explanation:

-Hash Index is best for exact match lookups (e.g., =).
-It is not used for range queries like > or <.

✨ Conclusion

In this experiment, we learned:

• How to create and use B-Tree, B+ Tree, and Hash indexes
• How these indexes improve query performance
• Which index type fits each kind of query