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)