🚀 Indexing, Hashing & Query Optimization in DBMS

Databases don’t just store data — they also need to retrieve it efficiently. When you’re dealing with thousands (or millions) of records, querying without proper optimization can slow your system to a crawl.

That’s where indexing and hashing come into play. These techniques help databases find data faster, just like how an index helps you locate topics quickly in a book.

Let’s dive deep into what they are, how they work, and when to use them.

🧩 What Is Indexing?

An index is a data structure that improves the speed of data retrieval from a database table.

Instead of scanning every row to find the required data, the database uses the index to jump directly to the location of the record.

Think of it as the index page of a book — it doesn’t store the entire content, but points you exactly where to find it.

🔹 Types of Indexing

Primary Index – Automatically created on the primary key column.

Secondary Index – Created manually by the user for faster access to non-key attributes.

Clustering Index – Determines how data is physically stored on disk.

Non-Clustering Index – Has a separate structure pointing to the physical records.

⚙️ B-Tree and B+Tree Indexes

Most modern databases use B-Tree or B+Tree indexing.

B-Tree Index: Balanced tree structure where both internal and leaf nodes can contain keys and data pointers.

B+Tree Index: Internal nodes only store keys; actual data is kept in the leaf nodes.

Leaf nodes are linked, making range queries and sequential scans faster.

📘 Use Case: Best for range queries like

SELECT * FROM students WHERE roll_no BETWEEN 10 AND 50;

🔢 Hash Indexing

A hash index uses a hash function to compute the location of a record based on its key. The hash value determines which “bucket” the record belongs to.

✅ Best for: Equality searches like

SELECT * FROM students WHERE roll_no = 45;

❌ Not good for: Range-based queries or sorting operations (like BETWEEN, <, >).

📘 Use Case: When your application frequently runs exact-match lookups.

🧾 Example: Students Table

Let’s create a sample table to see how indexing helps:

CREATE TABLE Students ( roll_no INT PRIMARY KEY, name VARCHAR(100), age INT, grade CHAR(1) );

Step 1: Create Indexes – B-Tree index (default) CREATE INDEX idx_roll_btree ON Students (roll_no);

– Hash index (if supported by your DBMS) CREATE INDEX idx_roll_hash ON Students USING HASH (roll_no);

Step 2: Run Queries – Equality check (best for hash or B-tree) SELECT * FROM Students WHERE roll_no = 50;

– Range query (best for B-tree or B+Tree) SELECT * FROM Students WHERE roll_no BETWEEN 10 AND 100;

👉 The B-Tree index handles both cases efficiently, while the hash index excels only in equality lookups.

📊 When to Use Which Index Use Case Best Index Reason Equality lookups (=) Hash or B-Tree Hash is fastest for exact matches Range queries B-Tree / B+Tree Maintains sorted order Sequential access B+Tree Linked leaf nodes improve performance Memory optimization Minimal indexing Too many indexes slow down inserts/updates ⚠️ Important Considerations

Storage Overhead: Every index consumes additional space.

Write Performance: More indexes = slower INSERT, UPDATE, DELETE.

Low-Cardinality Columns: Avoid indexing columns with few unique values (e.g., gender, status).

Maintenance: Indexes can fragment over time and may need rebuilding.

🧠 Query Optimization

Indexes are one of the most effective tools for query optimization. But you can combine them with:

Query planning: Use EXPLAIN to analyze how queries execute.

Proper filtering: Avoid SELECT *; fetch only what’s needed.

Composite indexes: Combine multiple columns in one index for common query patterns.

✅ Summary Concept Description Indexing Data structure for faster lookups B-Tree / B+Tree Supports ordering and range queries Hash Indexing Best for equality checks Query Optimization Uses indexes and execution plans for efficiency 🚀 Final Thoughts

Efficient indexing and query design are what make large-scale applications fast and reliable. Understanding how and when to use B-Tree or Hash indexes can significantly improve your database performance.

Start small — analyze your queries, create the right indexes, and monitor performance. A few thoughtful indexes can turn your slowest queries into instant results.