This content originally appeared on DEV Community and was authored by Ilakkiya
Have you ever noticed how some database queries return results almost instantly — even when the table has thousands of rows?
That’s the magic of Indexing, Hashing, and Query Optimization.
In this post, we’ll see how these techniques make databases faster and smarter — using a simple Students table as our example.
Step 1 — Creating the Students Table
Let’s start by creating the table and inserting 20 sample records to work with.
Step 2 — B-Tree Index on roll_no
B-Tree indexing is the default and most common type of index in relational databases.
It helps in quickly locating rows based on range queries or sorted data.
CREATE INDEX idx_rollno_btree
ON Students(roll_no);
Now, let’s use that index to fetch a student’s details efficiently.
Result: The database uses the B-Tree index to find the record in milliseconds.
Step 3 — B+ Tree Index on cgpa
A B+ Tree index is an enhancement of the B-Tree — perfect for range-based queries, such as finding all students with CGPA above a threshold.
Result: The database quickly retrieves qualifying students without scanning the entire table.
Step 4 — Hash Index on dept
Hash indexing is great for exact matches, such as looking up a department by name.
It uses hash functions to map keys directly to data locations — extremely fast for equality checks.
Result: The database directly jumps to all CSBS records using the hash key — no full scan required.
Step 5 — Query Optimization in Action
Indexes and hashing dramatically improve performance by reducing search time and optimizing query execution plans.
To see the difference, you can run:
EXPLAIN SELECT * FROM Students WHERE cgpa > 8.0;
Result: The plan shows the use of indexes, confirming optimized access paths.
Summary
- B-Tree Index – Ideal for range and sorted queries
- B+ Tree Index – Efficient for range lookups with dense leaf nodes
- Hash Index – Perfect for equality comparisons
- Query Optimization – The key to high-speed, low-latency data retrieval
Indexes are like shortcuts for the database — they make searching faster, queries smarter, and performance smoother.
SQL #Indexing #Hashing #QueryOptimization #Database #Learning #DevCommunity
This content originally appeared on DEV Community and was authored by Ilakkiya
Ilakkiya | Sciencx (2025-10-05T02:29:14+00:00) Indexing, Hashing & Query Optimization in SQL. Retrieved from https://www.scien.cx/2025/10/05/indexing-hashing-query-optimization-in-sql-2/
Please log in to upload a file.
There are no updates yet.
Click the Upload button above to add an update.