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 -- Dan Nelson dnel...@allantgroup.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
