Further reason DELETE FROM on a Merge Table is so inefficient is this: DELETE FROM tbl without a WHERE clause on a MyISAM Table is mapped internally to TRUNCATE TABLE, which would be instatntaneous
DELETE FROM tbl WHERE 1=1 would clearly make even a MyISAM table terribly ineffeicient as it must evaluate 1=1 on every row in the table. A Mrg_MyISAM (Merge) Table must always do some kind of WHERE evaluation since a query issued against a Merge Table must be evaluated on each of the mapped MyISAM tables. It would be nice if the Mrg_MyISAM engine were coded to map a 'DELETE FROM' without a WHERE clause to a DELETE FROM on all underlying MyISAM tables. ----- Original Message ---- From: Chris <[EMAIL PROTECTED]> To: mos <[EMAIL PROTECTED]> Cc: mysql@lists.mysql.com Sent: Tuesday, November 20, 2007 11:13:58 PM Subject: Re: Why is Delete slow on a Merge Table? mos wrote: > I have a merge table that is a union of 20 1 million row tables. Select > rows from it is quite fast. However if I want to delete all the rows as in: > > delete from MyMergeTable; > > it takes just over 3 minutes. I could execute 20 separate delete > statements for each of the 20 tables and it would complete in under a > second. Why is a Delete on a merge table so inefficient? It appears it > is deleting all the rows individually. That's the way delete works - it deletes the rows one at a time. It also has to update the indexes as it goes to remove links between the data & index. http://dev.mysql.com/doc/refman/5.0/en/delete.html http://dev.mysql.com/doc/refman/5.0/en/delete-speed.html If you're clearing the whole table, use truncate as Jerry suggested. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] ____________________________________________________________________________________ Get easy, one-click access to your favorites. Make Yahoo! your homepage. http://www.yahoo.com/r/hs
