John Smith John Smith - 1 year ago 66
SQL Question

How can I index my SQL table?

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):

  • Author

  • OverallRating

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.

Answer Source

You create an index in SQL with CREATE INDEX <nameOfIndex> ON <nameOfTable> (<field>,<field>,...); with one or more fields.

So for the first query how about CREATE INDEX Authors ON Review (Author);?

See if you can do the rest yourself. If you get stuck try looking through the lecture slides we're given and asking Google, you can't expect Stack Overflow to keep doing lots of your university coursework for you.