[gentoo-server] MySQL slow running selects on tables that *used* to be large

[Dice R. Random]

Hello list,

I have a fairly low end server (dual P3 450s, 768 megs of RAM) which
I'm running MySQL on.  The database is primarily used for logging
purposes and so it has a continually growing number of records.  I do
try to keep the number of records down by moving data off to an
archive table periodically.

Last night I noticed that selects on the logging table were taking
over a second to execute, sure enough the database was starting to get
a little big (~250000 entries).  I moved most of the data out to the
archive table:

INSERT INTO archive SELECT * FROM logs WHERE
timestamp<'some-time-about-an-hour-ago';
DELETE FROM logs WHERE timestamp<'some-time-about-an-hour-ago';

This left about 350 rows in the logs table and a quarter million more
in the archive.  The problem is that even with only 350 rows currently
in the table selects were still taking over a second to execute.  When
I first created this table I could run selects against it with a few
thousand rows and still keep in the hundredth of a second range.

Shouldn't 350 entries be 350 entries?  Why should it take longer to do
a select against a table which used to have a large number of entries
in it?

I'm using dev-db/mysql-4.0.25-r2 by the way.

Thanks for any help.
-- 
gentoo-server@g.o mailing list