> I basically have a situation where 300,000+ rows are being inserted into a
> log table daily, and also needing to be cleared out based on a
> tstamp. What
> I've found is that the inserts work fantastic, but when I got to do the
> delete, even if it doesn't do anything, it sits there and blocks
> everything
> on the server for 20-30 minutes.
There was quite an interesting article on that subject in the current issue
of a German computer magazine called "iX".
The biggest problem with deletes seems to be updating the indexes. This has
to be done (using the basic DELETE comamnd) for each deleted set.
Basically, there are 3 ways to delete:
1) Simply "DELETE WHERE ...": Used with small operations
2) DELETE without indexes: This is done by first deleting the indexes,
giving the DELETE command and then recreating the indexes. It is recommended
to use this way with tables holding more than 10000 data sets, when there
are more than 3% of all sets to be deleted.
3) MOVE-RENAME: First, all data sets that are not to be deleted are copied
to a temporary table, then the original table is DROPped, and finally the
temporary table is renamed to the original one's name. This method is
recommended under the following circumstances:
Number of data sets | Percentage of sets to be deleted
>= 200000 | >=80%
>= 400000 | >=70%
>= 700000 | >=60%
>= 1000000 | >=50%
HTH, Jens
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
