DEV Community

Cover image for Indexing, Hashing & Query Optimization
SRIRAM PG
SRIRAM PG

Posted on

Indexing, Hashing & Query Optimization

Step-1: 1. Setting Up Your Table
First, you'll create a simple table to store student information and fill it with some data.

Create a table Students: You need a table with columns for roll number (roll_no), name, department (dept), and cgpa.

Insert at least 20 sample records: You'll add 20 or more rows of fake student data into this table.

This gives you a dataset to work with. Without any indexes, if you search for a student, the database has to look through every single row one by one, which is slow.

Step-2: B-Tree Index (For Specific Lookups)
This part shows how to speed up searches for a single, specific record.

Create a B-Tree index on the roll_no column: A B-Tree index is like the index at the back of a textbook. If you want to find a specific topic (like roll_no = 110), you don't read the whole book; you look it up in the index, which tells you the exact page number (or in this case, the row's location). This is very fast for finding unique values.

Execute a query to fetch the details of a student with roll_no = 110: When you run this query, the database will use the B-Tree index you just created to instantly find the student with roll_no = 110 instead of scanning the whole table

Step-3: B+ Tree Index (For Range-Based Searches)
This part focuses on searches that look for a range of values.

Create a B+ Tree index on the cgpa column: A B+ Tree is a special type of B-Tree where all the final data pointers are stored at the bottom level (leaf nodes) and are linked to each other. This structure makes it extremely efficient to scan through a sequence of data.

Write a query to display all students with cgpa > 8.0: This is a range query (you're not looking for one CGPA, but all CGPAs above a certain value). The B+ Tree allows the database to quickly find the first student with a CGPA of 8.0 and then just follow the linked list to get all the other students with higher CGPAs.

Step-4: Hash Index (For Exact Matches)
This part demonstrates an index that is super fast for finding records based on an exact value.

Create a Hash index on the dept column: A Hash index works like a dictionary or hash map in programming. It takes a value (e.g., 'CSBS'), converts it into a unique code (a "hash"), and uses that code to find the data's location directly.

Run a query to retrieve all students from the 'CSBS' department: This is an equality query. The hash index will instantly find all students in the 'CSBS' department. However, a hash index is not good for range queries (e.g., dept > 'CSBS').

Top comments (0)