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.
- 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)