Reputation: 3799
Ive got a table with a many rows, currently no fields are unique. I've got a userid field and an gameid field and some other rows storing information on the games the users have played. As a user plays the game the score is updated so there are quite alot of update queries happening on this table and it's starting to get pretty large.
Would it be beneficial adding another field thats an index and then storing a string such as userid_gameid which would then mean updates were faster in the table if I do my update queries so where index=10_10 (example)
Thanks
Upvotes: 1
Views: 48
Reputation: 25018
Don't add a noddy field for userid + gameid, instead, create an index that includes both columns. If the two columns taken together are intended to be unique then make this the primary key of the table.
CREATE INDEX myIndex ON myTable (userid, gameid)
Upvotes: 4