Question

An OTT company is maintaining a large disk-based relational database of different movies with the following schema :
Movie (ID, Customer Rating)
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.Genre ID = 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.

Answer 1

The Correct Option is A, B

Option (A) specifies a B⁺ tree on all attributes. Option (B) specifies a Hash index on Genre.Name and a B⁺ tree on the remaining attributes.