DEV Community

Rajalakshmi
Rajalakshmi

Posted on

Indexing, Hashing & Query Optimization

Indexing
Indexing is a technique to speed up data retrieval in a database. It creates a separate data structure (index) that helps locate records quickly without scanning the entire table.

Common index types:

B-Tree Index – used for searching and sorting.
B+ Tree Index – efficient for range queries.
Hash Index – used for exact match lookups.

Hashing

Hashing uses a hash function to convert a key (like dept) into a hash value that points to the location of the record.

It provides fast access for equality searches but is not suitable for range-based queries.

Query Optimization

Query Optimization is the process of choosing the most efficient way to execute a query.

The optimizer analyzes indexes, joins, and conditions to minimize query execution time and improve performance.

  1. Create a table Students with fields (roll_no, name, dept, cgpa)

2 . Insert at least 20 sample records.

3 . Create a B-Tree index on the roll_no column of the Students table.

4 . Execute a query to fetch the details of a student with roll_no = 110.

5 . Create a B+ Tree index on the cgpa column of the Students table.

6 . Write a query to display all students with cgpa > 8.0.

7 . Create a Hash index on the dept column of the Students table.

8 . Run a query to retrieve all students from the 'CSBS' department.

Top comments (0)