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
Primary index in sequential order file organisation is also known as ______.
A B-tree used as an index for a large database table has four levels including the root node. If a new key is inserted in this index, then maximum number of nodes that could be newly created in the process is
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)
- 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?
