Ok. The second one below sounds semi reasonable - it does mean that the
table will be somewhat useless for queries during the delete, but that's not
tragic. Is there any way to drop all the indexes on a table with a single
statement?
Third one sounds good as well, but unfortunately, there is a steady stream
of data being added to the table. I suppose if I were to dynamically be
creating new tables all the table for the data, then it might be reasonable
to do that, but that's probably more trouble than it's worth for this
application.
The percentage to be deleted will be anywhere from 3-5%.
Jeremy also mentioned trying out innobase tables... I might give them a shot
as well.
-- Nathan
> -----Original Message-----
> From: Jens Vonderheide [mailto:[EMAIL PROTECTED]]
> Sent: Monday, March 19, 2001 3:49 PM
> To: Neulinger, Nathan R.; [EMAIL PROTECTED]
> Subject: AW: suggestions for implementing heavily used log tables with
> nightly delete
>
>
> > 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
