mysql 4.0.18 got slow.. 2

[Yrrk]

I have mysql 4.0.18 installed on redhat9. When i first set it up over a year ago, it was extremelly fast on the queries it ran. I run those same queries today and it takes quite a while to come back (5-10 seconds?). I've tried restarting mysqld, rebooting the box, and even ran a command i read in some mysql performance tuning recommendations but nothing has helped at all.

The database is actually quite small. Two tables with a few thousand rows. If anyone has any suggests, commands i should run, etc.. I would appreciate any help on how to look into why this is occuring.

 

[towerbase]

Usually when I have had a performance problem like this I have found the cause to be a missing Index.

Check that that there are indexes appropriate to the queries you are experiencing problems on.

Andrew
Hampshire, UK

 

[Yrrk]

I spoke to someone internal here and he looked at one of my queries and sure enough doing the following made it faster..

alter table Stats add key (IP);

The difference was striking and i take it the reason he chose IP was because of the group by clause. So I tried another query that gets run repeatedly by a cgi script and noticed though it was noticably faster, it wasn't as fast as i'd have expected. Here is the query:

select DateTime,Used,Avail,MPS,Sending,Receiving,NumSMAP,NumSenders,IP,AvgSize,Perc from Stats where IP='$IP' and DateTime='$DateTime' group by IP;

So I went back to the individual and he had me do a:

alter table Stats add key (IP, DateTime);

Again the difference was striking. It loads instantaniously now. Whats the rule of thumb on when to index a field and when not to? I notice the later query doesn't do a group by with datetime, only IP like the original query. But clearly indexing datetime had a huge impact here as well.

The way it was explained to me, without the index mysql builds a temporary table to do it's query which is what causes the slowness. With the index it doesn't need to do this. Sounds great but how do i evaluate in the future when i need to do it and when not? Any field used in the group by or where clause? Or can someone point me to the right spot in the documentation on where i should do some reading?

thanks!

 

[nego78]

Good material:

http://dev.mysql.com/doc/mysql/en/where-optimizations.html

http://dev.mysql.com/doc/mysql/en/order-by-optimization.html

gry online