An index on a constantly changing table?


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?

Thanks )


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

Could the primary key actually be a combination of the three columns:


In that order.

heh, was about to post to say i’d found out that you can create a key on more than one field. So i’ve created it on GameID and UserID and the command went through, wether it improves the performance… only time will tell )

Any other performance tips for a database of this kind? Like as a couple of the tables are getting rows deleted, added and ammended very regularly, is it efficient to otimize the tables on a regular basis (like, every few minutes) or should I just have it run daily?

You definitely want to make use of the ability to create indexes on more then one column.

Look at the queries that you use often, and try to plan your where clauses and indexes accordingly.

Based on this one query, it appears that the index I suggest may help. Let me know what you find.

Thanks for the advice. But (forgive my noobishness) does the table need optimising very regularly to keep this effective? That table is getting shuffled around thousands of times per minute, will the indexes stay effective if it’s only optimized daily? hourly? every 15 minutes?