An index on a constantly changing table?

[shavenlunatic]

Hi,

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

________
clueless

 

[ingresman]

If you have a column in a where clause it's probabbly a condidate for an index.
Remember that index's need to be maintained so if you update a column that has 6 indexes on it the 6 index all need updating as well.
Try not to have indexes with the same column at the left hand side e.g. (id,name) or (id,address).
Sometimes indexes don't have the desired effect as they increase the underlying stress on the disks, try to place indexes on different disks to the main table
Have you considered looking at something like memcached to hold read only stuff rather than hitting the database ?

 

[shavenlunatic]

I'm just not sure how to go about this. I'll show a cut down example of how the database looks, to give an idea of where my problem lies. Problem is, when we query that database we are sending through 2 conditions in the WHERE.

GameID, UserID, GameInfo1, GameInfo2, GameClosed
1, 100, x, y, 0
1, 130, a, b, 0
2, 175, g, h, 0
2, 122, r, s, 0

So when a game is complete, it sets GameClosed to 1. The userid will have duplicate entries, the gameid will always have 2 entries.

When we are querying it would be along the lines of

WHERE GameID=999 AND UserID=100 AND GameClosed=0

so there is (with myvery basic knowledge) no field I could set an index as, unless I created a new field which just auto-increments when a new record is created but isn't actually queries. Would there be any point in this or does it look like we are going to have to toally rethink the way games are managed?

________
clueless

 

[hoinz]

You seem to believe that an index has to be unique. This is not true. Just omit 'UNIQUE' keyword when creating.
And a non-unique index may speed up a query as well.

hope this helps

 

[ingresman]

You could create an index on gameid as a unique key and store that in session. Whenever you needed to get to the record just use that value in the where clause

 

[shavenlunatic]

You seem to believe that an index has to be unique. This is not true. Just omit 'UNIQUE' keyword when creating.
And a non-unique index may speed up a query as well.

i wasn't aware you could do that... i'll take a look into it and see if it helps, so thanks

You could create an index on gameid as a unique key and store that in session. Whenever you needed to get to the record just use that value in the where clause

how can i create an index on gameid as a unique key, then there are duplicate entries? (2 to be exact)

________
clueless

 

[ingresman]

first create index fred(gameid,userid,gameclosed) on <table> should get you started.
If this combination is not unique how do you know whch row to update ?
When I said a unique id , this is seperate from the user data and is an ascending key automaticlay created by mysql.
Do you think it might be worthwhile to get yourself a book on mysql ?, your site seems to take a lot of hits. A good book might help you in many ways.

 

[ingresman]

sorry should be create index fred on <table>(gameid,userid,gameclosed)

 

[shavenlunatic]

cool, that's worked, now I'll see how performance goes.

Thanks very much appreciate it

________
clueless