This content originally appeared on DEV Community and was authored by srivishal
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.
This content originally appeared on DEV Community and was authored by srivishal
srivishal | Sciencx (2025-10-05T18:35:29+00:00) Indexing, Hashing, and Query. Retrieved from https://www.scien.cx/2025/10/05/indexing-hashing-and-query/
Please log in to upload a file.
There are no updates yet.
Click the Upload button above to add an update.