> -----Original Message----- > From: Dan Nelson [mailto:dnel...@allantgroup.com] > Sent: Wednesday, April 14, 2010 7:23 AM > To: David Florella > Cc: mysql@lists.mysql.com > 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; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
