I have two tables, the first is called Hotel and contains the following columns:
hotelID, AverageRating, OverallRating, URL
The second is called Review and contains:
hotelID, Author, Content, Date, No. Reader, No. Helpful, Overall, Value, Rooms, Location, Cleanliness, Checkin / front desk, Service, Business Service
I have been asked to create relevant indexes on my tables based on my queries. Here are my four queries:
- Find all the reviews by the same user (i.e., given a user ID, return
the list of all her reviews).
- Find all the users with the number of reviews greater than 2 and
return their name and number of hotels they reviewed for.
- Find all the hotels with the number of reviews greater than 10.
- Find all the hotels with overall rating greater than 3 and average
cleanliness greater or equal to 5 (Note: use the Overall Rating attribute).
Based on these queries I would need to index the following columns (I think):
I would not need to index cleanliness as this is an average so indexing would have no benefit.
The Author column contains the username for the reviewer. For example, Quoka7. Would it be best to index this column by alphabet perhaps?
The OverallRating column contains a decimal (to 1 d.p) between 1.0 and 5.0. For example, 3.5. Would it be best to index this column to 1 d.p perhaps?
Lastly, would it be beneficial to me to index the hotelID column? This basically has the format hotel_* where * is a unique number for each hotel. Based on my queries I do not think this would be useful.
Thanks for your time, I'm here if you have any questions or need more info.
Author can also start with a number so perhaps alphabet not ideal.