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

[dormando]

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.

-- 
gentoo-server@g.o mailing list