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)