> -----Original Message-----
> From: Dan Nelson [mailto:dnelson@stripped]
> Sent: Wednesday, April 14, 2010 7:23 AM
> To: David Florella
> Cc: mysql@stripped
> Subject: Re: Make delete requests without impact on a database
>
> In the last episode (Apr 14), David Florella said:
> > I am using MySQL version 4.1.12-log. All the databases on
> it are using
> > MyISAM database engine.
> >
> > Every day, I delete almost 90000 rows on a table of 3 153 916 rows.
> >
> > To delete the rows, I use a request like this : "DELETE
> QUICK FROM [table]
> > WHERE [column] < '2010-04-13 00:00:00' LIMIT 7500". I execute this
> > request until all the rows are delete.
> >
> > This works but when I run the request, I can't access to
> the database
> > (make INSERT and SELECT requests) during I do the DELETE.
> >
> > How can I do a "DELETE" without impact on INSERT and SELECT
> requests done
> > on the same time?
>
> Switch to InnoDB :) The MyISAM engine has to lock the entire
> table during
> write queries, so all queries have to wait for slow
> UPDATE/INSERT/DELETE
> calls to complete. An alternative would be to lower your
> LIMIT even more;
> say to 1000. Then you'll do 90 very small deletes instead of
> 12 smallish
> ones. Hopefully you're doing this loop in a program somewhere and not
> manually running the deletes from a mysql CLI prompt... You
> may also want
> to add the LOW_PRIORITY keyword to your DELETE statement;
> that will keep the
> DELETE from moving to the front of the queue if there are other SELECT
> statements pending.
>
> http://dev.mysql.com/doc/refman/5.1/en/delete.html
Another option to try is make a new column called "purge", then instead of
DELETE, use UPDATE to set the flag to 1 or something. Then at night or when
you have a slow time, run your DELETE WHERE `purge` = 1;
