Indexing
Indexing is a method used to speed up the retrieval of data from a database. It works by creating a separate structure (called an index) that allows the database to find records quickly without scanning the entire table.
Common types of indexes include:
B-Tree Index – ideal for search operations and sorting data.
B+ Tree Index – optimized for queries that involve ranges.
Hash Index – best suited for exact match searches.
Hashing
Hashing involves applying a hash function to a key (such as a department name) to generate a hash value, which directly points to the location of the record.
It provides fast access for equality searches.
It is not efficient for range queries, since hash values do not maintain any order.
Query Optimization
Query optimization is the process of determining the most efficient way to run a database query.
The query optimizer examines indexes, join methods, and conditions.
The goal is to reduce execution time and improve overall 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)