🚀 Indexing, Hashing & Query Optimization in SQL (Tutorial Example on Students Table)

In database systems, query performance is critical. If a table has thousands (or millions) of rows, scanning the entire table for every query is slow. Indexes and hashing solve this problem by allowing faster data retrieval.

Indexing works like a textbook index — instead of scanning every row, the database can jump directly to the required data.
Hashing uses a hash function to quickly locate records based on a search key.

Types of Indexing We’ll Explore:

• B-Tree Index → Best for equality and range queries.
• B+ Tree Index → Optimized for sequential and range queries.
• Hash Index → Very fast for equality searches but not for ranges.

In this tutorial, we’ll create a Students table and see how different indexes improve query performance.

📌 Step 1: Create the Students Table:

We created a database named College to store our tables. Using a separate database helps keep data organized and isolated from other projects.

The table consists of:

• roll_no → Unique identifier for each student (primary key).
• name → Student’s name.
• dept → Department (e.g., CSE, CSBS).
• cgpa → Cumulative grade point average (decimal). This table will be used to demonstrate indexing.

📌 Step 2: Insert Sample Records

Inserted 20 records covering different departments and CGPA values to demonstrate range and equality queries.

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

B-Tree indexes are excellent for equality lookups and range searches.

📌 Step 4:Query : Student by Roll Number

Searching for a specific roll_no is fast because the database can navigate the tree instead of scanning all rows.

📌 Step 5: Create a B+ Tree Index on cgpa

B+ Tree indexes store all records in leaf nodes, making sequential and range queries efficient.

📌 Step 6:Query : Student by Cgpa>8.0

Queries like cgpa > 8.0 can quickly access only the relevant CGPA values without scanning the entire table.

📌Step 7: Create a Hash Index on dept

Hash indexes are extremely fast for equality searches (like dept = 'CSBS').

📌Step 8: Query : By Department

📌Step 9: Test Query Performance

EXPLAIN shows the query execution plan.
After creating indexes, the database uses the index instead of scanning all rows, improving speed.

🎯 Conclusion

Indexing and hashing are essential tools for query optimization in database systems. By creating the right type of index:

• B-Tree helps quickly locate individual records and supports range queries.
• B+ Tree is ideal for efficiently retrieving records over a range of values.
• Hash Index provides lightning-fast lookup for equality searches.

Proper use of indexes can dramatically reduce query time, improve database performance, and make large datasets manageable. Always choose the index type based on your query patterns for optimal results.