Run OPTIMIZE TABLE on the table after doing mass deletes like that.
(especially for MyISAM).
-Dormando
Dice R. Random wrote:
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.
--
[email protected] mailing list
