At 08:50 AM 11/21/2007, Rolando Edwards \(DBA\) wrote:
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.
Agreed. That was the point I was trying to make. It should be optimized. As
I mentioned to Jerry, the only solution is to use Truncate on each table
and if the merge definition changes, I have to remember to remember to
update the deletes as well.
Mike
----- 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.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>http://lists.mysql.com/mysql
To
unsubscribe:
<http://lists.mysql.com/[EMAIL PROTECTED]>http://lists.mysql.com/[EMAIL PROTECTED]
Get easy, one-click access to your favorites.
<http://us.rd.yahoo.com/evt=51443/*http://www.yahoo.com/r/hs>Make Yahoo!
your homepage.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
