Developing a web game and looking to optimise the MySQL back end, fairly new to databases and I KNOW there are things I could be doing to optimize it.
The game pretty much constantly hits the database, the queries are great, filtering out what isn’t needed and only returning the exact information required.
Some of the tables are hit for information pretty much constantly (hundred queries a second) and are contantly updated. (game uses ajax and fires 5 queries, per player, every 3 seconds, not including manual events)
My main question is:
There are a couple of these rapidly updated tables which don’t have indexes, is it worth while adding an index to these to increase performance? There isn’t currently a unique field which could be indexed on, so I would be purely creating a field for index purposes
And my sub-question is:
Are there any other optimisation tips you have for a database being used for these purposes?
To add to that, and give an idea of hwo the table is laid out. (more info than this, but as this table stores historical data I wanted to explain why an index doesn’t currently exist) gameclosed is changed to 1 when game has ended so there can be duplicate userid and gameid… we may possibly need to rething the way it manages the game list, but I was hoping that we could get some joy out of sticking an index in there
GameID, UserID, GameInfo1, GameInfo2, GameClosed1, 100, x, y, 01, 130, a, b, 02, 175, g, h, 02, 122, r, s, 0
Our queries would be along the lines of:
WHERE GameID=999 AND UserID=100 AND GameClosed=0