DEV Community

Cover image for INDEXING, HASHING AND QUERY OPTIMIZATION
Santhosh_M
Santhosh_M

Posted on

INDEXING, HASHING AND QUERY OPTIMIZATION

Indexing, Hashing & Query Optimization in SQL: A Practical Guide

As databases grow, queries on large tables can become slow. Indexing is one of the most effective ways to improve performance. In this blog, we’ll explore B-Tree, B+ Tree, and hash-like indexing, and see how they optimize SQL queries with a practical example.

  1. Setting Up the Students Table

We’ll use a Students table with 20 sample records:

CREATE TABLE Students (
    roll_no INT PRIMARY KEY,
    name VARCHAR(50),
    dept VARCHAR(20),
    cgpa DECIMAL(3,2)
);

INSERT INTO Students (roll_no, name, dept, cgpa) VALUES
(101, 'Alice', 'CSBS', 9.2),
(102, 'Bob', 'CSBS', 7.8),
(103, 'Charlie', 'ECE', 8.1),
(104, 'David', 'MECH', 6.9),
(105, 'Eva', 'CSBS', 8.5),
(106, 'Frank', 'ECE', 7.5),
(107, 'Grace', 'MECH', 9.0),
(108, 'Hannah', 'CSBS', 8.8),
(109, 'Ian', 'ECE', 7.2),
(110, 'Jack', 'CSBS', 9.1),
(111, 'Kate', 'MECH', 8.0),
(112, 'Leo', 'ECE', 8.3),
(113, 'Mona', 'CSBS', 7.9),
(114, 'Nate', 'MECH', 6.8),
(115, 'Olivia', 'ECE', 9.0),
(116, 'Paul', 'CSBS', 8.6),
(117, 'Quinn', 'MECH', 7.5),
(118, 'Rita', 'ECE', 8.4),
(119, 'Steve', 'CSBS', 9.3),
(120, 'Tina', 'MECH', 8.1);
Enter fullscreen mode Exit fullscreen mode
  1. B-Tree Index on roll_no

B-Tree indexes are ideal for exact matches and small-range queries:

CREATE INDEX idx_roll_no ON Students (roll_no);

SELECT * FROM Students WHERE roll_no = 110;
Enter fullscreen mode Exit fullscreen mode

The database uses the index to quickly locate the student without scanning the entire table.

  1. B+ Tree (Ordered B-Tree) on cgpa

B+ Tree indexes are excellent for range queries:

CREATE INDEX idx_cgpa ON Students (cgpa);

SELECT * FROM Students WHERE cgpa > 8.0;
Enter fullscreen mode Exit fullscreen mode

Using the index, the database scans only relevant rows, improving query speed.

  1. Index on dept for Exact Matches

While hash indexes are supported in some databases (like PostgreSQL), SQLite and most online editors use B-Tree indexes, which are still effective for equality checks:

CREATE INDEX idx_dept ON Students (dept);

SELECT * FROM Students WHERE dept = 'CSBS';
Enter fullscreen mode Exit fullscreen mode

Indexing dept speeds up exact-match queries for departments.

  1. Indexing Benefits at a Glance
Index Type Column Best Use Case
B-Tree roll_no Exact matches & small ranges
B+ Tree cgpa Range queries (>, <, BETWEEN)
B-Tree dept Exact-match queries (=)

⚠️ Note: Indexes consume storage and may slow INSERT/UPDATE. Use them wisely.

  1. Query Optimization Tips

  2. Index frequently used columns in WHERE, JOIN, ORDER BY clauses.

  3. Avoid functions on indexed columns (e.g., UPPER(name)) as they may bypass indexes.

  4. Use EXPLAIN to see if a query is using an index:

EXPLAIN SELECT * FROM Students WHERE cgpa > 8.0;
Enter fullscreen mode Exit fullscreen mode
  1. Choose the right index type:
  • Range queries → B+ Tree
  • Exact matches → Hash or B-Tree

Conclusion

Indexing is a powerful tool to speed up SQL queries and improve application performance. By using B-Tree, B+ Tree, and hash-like indexes, you can optimize searches, range queries, and exact-match lookups efficiently.

Try it yourself: create indexes, run queries, and see the difference in query execution!



Top comments (0)