INDEXING,HASHING & QUERY OPTIMIZATION IN SQL (EXAMPLE ON STUDENTS TABLE )

📘 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.