DEV Community

Cover image for Indexing, Hashing & Query Optimization in SQL
Ilakkiya
Ilakkiya

Posted on

Indexing, Hashing & Query Optimization in SQL

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);
Enter fullscreen mode Exit fullscreen mode

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.

HASH index using an in-memory table:

A MEMORY table is stored in RAM. If the database server restarts, the MEMORY table disappears. Use it only for temporary, very fast lookups.

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;
Enter fullscreen mode Exit fullscreen mode


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

Top comments (0)