Jack
Jack

Reputation: 3799

Would an index on a table be beneficial in this case?

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

Answers (1)

Will A
Will A

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

Related Questions