Database indexes - what should they be
- by WebweaverD
Most of my database tables have a clear unique index through which lookups are done 90% of the time but I am a bit unsure on this one - I have a table which keeps track of user rating totals for items in my database, I now want to add another table, to track individual ratings with an ip address column to make sure no one can rate something twice. Since I can see this becoming a big, high use table it is important to optimize it correctly. (MYSQL table)
This table will have the following fields:
rating_id(always - unique), item_id (always - not unique), user_id (optional - not unique), ip_address (always - not unique), rating_value(always - not unique), has_review(bool)
Now I envisions 90% the queries going something like this:
When a user rates something - select where item_id = x and ip_address = y, (if rows = 0) insert rating
When in user account pages - select where ip_address = x or username = y
Now none of the fields searched on are unique, can I still use them as indexes (for example item _id and ip_address), can I have two indexes and will this still improve performance over a non indexed table?
