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(10),
    cgpa DECIMAL(3,2)
);

`
🧾** Step 2: Insert Sample Records**

`
INSERT INTO Students VALUES
(101, 'Aarav', 'CSBS', 8.9),
(102, 'Diya', 'ECE', 7.8),
(103, 'Karthik', 'IT', 9.1),
(104, 'Meena', 'CSBS', 8.2),
(105, 'Rohit', 'EEE', 7.6),
(106, 'Isha', 'MECH', 8.0),
(107, 'Ravi', 'CIVIL', 7.4),
(108, 'Sneha', 'IT', 9.3),
(109, 'Vikram', 'CSBS', 8.7),
(110, 'Priya', 'CSE', 9.0),
(111, 'Hari', 'ECE', 8.4),
(112, 'Ananya', 'EEE', 7.9),
(113, 'Kavin', 'MECH', 8.1),
(114, 'Swathi', 'IT', 9.5),
(115, 'Teja', 'CSBS', 8.8),
(116, 'Vishwa', 'CSE', 9.2),
(117, 'Divya', 'ECE', 7.7),
(118, 'Suresh', 'CIVIL', 8.3),
(119, 'Kiran', 'IT', 8.6),
(120, 'Pooja', 'CSBS', 9.4);

`
🌳 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_bplustree ON Students(cgpa);

`

✅** Query:**

`
SELECT * FROM Students WHERE cgpa > 8.0;

`

⚙️ Step 5: Create a Hash Index on dept

`
CREATE INDEX idx_dept_hash ON Students USING HASH(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 <.