DEV Community

Rohivarshini Saravanan
Rohivarshini Saravanan

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)