Weird problem, queries seem to start going really slow?

[Kryptix]

I've posted about this before but I still haven't resolved this.

 

I run a online game which is SQL hungry, and when a player logs in it executes around 8 queries instantly. If we restart the game server and hundreds of players are trying to login it will only accept one every second or two when it's going slow, which is far too long and causes other bits to mess up. When it's running properly it's one every 0.1-0.4 second but I don't know why it's going slow.

 

I found out ages ago that my index was wrong and after speaking to a friend we got it fixed and it was perfect for about 2 weeks and then randomly it just went really slow again. I messed around with the indexes again, pretty much just removing them and adding it back and it fixed it. A few weeks later and it happens again...

 

This time, no matter what I try I can't get it to go back to normal. I just put up with it for ages and when I transferred to another host it worked again and was perfect but again, a week or two later and it went slow again. I then dropped the database and re-imported it which fixed it again for a few weeks, but now no matter what I do, nothing is resolving it.

 

I'm at my wits end, it's sooo annoying and I can't find any pattern with it. Why would queries start to just go slow? Is there anything I can do to speed them up? What's the optimize table button do (etc)?

[jskywalker]

you dont give information about your database,

like version, and query's and tables and stuff....

 

*jskywalker meselfs like a sccript called mysqltuner (http://blog.mysqltuner.com/) which gives some hints about improving performance.

 

you can try that...