An OTT company is maintaining a large disk-based relational database of…
2024
An OTT company is maintaining a large disk-based relational database of different
movies with the following schema:
Movie(ID, CustomerRating)
Genre(ID, Name)
Movie_Genre(MovieID, GenreID)
Consider the following SQL query on the relation database above:
SELECT * FROM Movie, Genre, Movie_Genre
WHERE Movie.CustomerRating > 3.4 AND Genre.Name = “Comedy” AND Movie_Genre.MovieID = Movie.ID AND Movie_Genre.GenreID = Genre.ID;
This SQL query can be sped up using which of the following indexing options?
- A.
B+ tree on all the attributes.
- B.
Hash index on Genre.Name and B+ tree on the remaining attributes.
- C.
Hash index on Movie.CustomerRating and B+ tree on the remaining attributes.
- D.
Hash index on all the attributes
Attempted by 108 students.
Show answer & explanation
Correct answer: A, B
Key insight: use B+ tree indexes because they support both equality and range queries and enable efficient index scans for joins.
Movie.CustomerRating should be indexed with a B+ tree to accelerate the range predicate CustomerRating > 3.4.
Genre.Name can be served by a B+ tree as well: it handles equality lookups and can also participate in ordered scans if needed.
Index the join keys (Movie.ID, Genre.ID, Movie_Genre.MovieID, Movie_Genre.GenreID) with B+ trees to speed join operations (index nested-loop or merge joins).
Why not hash indexes?
Hash indexes only support exact-match lookups and cannot accelerate range predicates like > 3.4 or provide ordered scans required by some join strategies, so they are not suitable as the sole index type for this query.