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?
The problem asks for the best indexing strategies to optimize a given SQL query on a relational database containing movie information.
Query Analysis
The SQL query involves selecting movie data by joining three tables: $Movie$, $Genre$, and $Movie_Genre$. It applies two main conditions:
- A range condition on $Movie.CustomerRating$ ($Movie.CustomerRating > 3.4$).
- An equality condition on $Genre.Name$ ($Genre.Name = “Comedy”$).
- Join conditions linking the tables via movie and genre IDs.
Effective indexing should address these filtering and join conditions.
Indexing Strategy Evaluation
Option A: B+ tree on all attributes
A B+ tree index is suitable for both equality searches (like $Genre.Name = “Comedy”$) and range searches (like $Movie.CustomerRating > 3.4$). It also efficiently supports join operations. Indexing all relevant attributes ($Movie.ID$, $Movie.CustomerRating$, $Genre.ID$, $Genre.Name$, $Movie_Genre.MovieID$, $Movie_Genre.GenreID$) provides a general performance improvement for the query's various clauses.
Option B: Hash index on Genre.Name and B+ tree on remaining
A hash index is highly efficient for exact match (equality) lookups. Using it on $Genre.Name$ directly speeds up finding the "Comedy" genre. B+ trees are used for other attributes, which effectively handles the range query on $Movie.CustomerRating$ and supports the join operations on the ID columns.
Why other options are less suitable:
- A hash index on $Movie.CustomerRating$ (Option 3) is inefficient for the range query $Movie.CustomerRating > 3.4$.
- A hash index on all attributes (Option 4) is unsuitable for the range query on $Movie.CustomerRating$.
Conclusion
Both Option A and Option B offer valid and effective indexing strategies. Option A provides robust performance across all conditions using B+ trees. Option B optimizes the equality lookup on $Genre.Name$ using a hash index while relying on B+ trees for range queries and joins, which is also a highly effective approach.
Important Questions from Indexing
- In a relational database, a B+ Tree Index is to be constructed for a relation on a key field. In a B+ Tree, a Node Pointer points to a sub-tree and a Data Record Pointer points to a block of database records.
Let, Node size = 4096 bytes, Node Pointer size = 10 bytes, Search Key Field size = 11 bytes and Data Record Pointer size = 12 bytes.
The maximum number of Node Pointers that can be present in a non-leaf node of the B+ Tree is ________ . (Answer in integer) Consider a B+ Tree where the maximum number of key values in each leaf node is 2 and the maximum number of pointers in each non-leaf node is 3. Let the content of the B+ Tree be as shown in the figure.
Which of the following options denotes the key value(s) stored in the root node after inserting a key value 3 in the given B+ Tree?
Primary index in sequential order file organisation is also known as ______.
The total cost of retrieving records in sorted order using an unclustered B+ tree is
(P-Average number of records per data page
N-Data pages
F-Ratio of the size of a data entry to the size of a data record)
- Consider $B^+$ tree in which the maximum number of keys in a node is 5. What is the minimum number of keys in any non-root node?
