Indexing, Hashing, and Query

Indexing involves creating a data structure (an index) that stores a small, ordered subset of data from a table, along with pointers to the full data records. When a query is executed, the DBMS can use this index to quickly locate the relevant data without scanning the entire table.

Hashing uses a hash function to directly map data values to their physical storage locations on disk. Instead of traversing an index structure, the hash function calculates the address of the data based on its value.

Create a table

INSERT INTO Students VALUES
(101, 'Arun', 'CSBS', 8.5),
(102, 'Mathan', 'ECE', 8.8),
(103, 'Karthik', 'MECH', 6.9),
(104, 'Hareesh', 'CSE', 9.1),
(105, 'Ravi', 'EEE', 7.2),
(106, 'Srivishal', 'CSBS', 8.8),
(107, 'Vignesh', 'IT', 8.0),
(108, 'Harish', 'CSE', 9.3),
(109, 'Deepak', 'ECE', 7.5),
(110, 'Nidheesh', 'CSBS', 9.0),
(111, 'Pradeep', 'MECH', 6.8),
(112, 'Lokhitha', 'EEE', 7.6),
(113, 'Raj', 'CSBS', 8.7),
(114, 'Divya', 'IT', 8.4),
(115, 'Saravanan', 'CSE', 9.2),
(116, 'Monika', 'ECE', 7.9),
(117, 'Ganesh', 'MECH', 6.7),
(118, 'Kavya', 'CSBS', 9.1),
(119, 'Surya', 'EEE', 7.3),
(120, 'Anitha', 'IT', 8.2);

Create a B-Tree Index on roll_no
CREATE INDEX idx_rollno ON Students(roll_no);

Query Using B-Tree Index

SELECT * FROM Students WHERE roll_no = 110;

Create a B+ Tree Index on cgpa

SELECT * FROM Students WHERE cgpa > 8.0;

Query Using Hash Index

SELECT * FROM Students WHERE dept = 'CSBS';

Indexing is generally preferred for queries involving range searches, sorting, or when frequent data modifications occur, as it offers more flexibility.
Hashing is highly effective for exact-match queries (equality searches) and when the primary goal is fast, direct access to individual records.