Bug #30743 Delete from MERGE-table is slow
Submitted: 31 Aug 2007 8:05 Modified: 26 Apr 2009 4:21
Reporter: Sergey Spatar Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Merge storage engine Severity:S3 (Non-critical)
Version:5.0.37-community/5.1/6.0 OS:Any
Assigned to: CPU Architecture:Any

[31 Aug 2007 8:05] Sergey Spatar
Description:
Delete from MERGE table takes much more time than summary time needed to delete from all merged tables.

It's not mentioned in documentation, but I guess delete from MERGE table produces multi-table delete operation, because multi-table deletes (even from a single table) are very slow too. Am I right with my guess?

How to repeat:
create table t_10m (c1 varchar(100), c2 varchar(100)) engine=myisam;
create table m_10m (c1 varchar(100), c2 varchar(100)) engine=merge union=(t_10m);

Then fill in t_10m with 10 million records with random text values for columns c1 and c2. Concrete data doesn't matter.

Test 1:

mysql> delete from t_10m;
Query OK, 10000000 rows affected (0.23 sec)

Test 2:

Refill table t_10m with 10 million records.

mysql> delete from merge_10m;
Query OK, 10000000 rows affected (1 min 18.68 sec)

In this example delete from MERGE table is 342 times slower than delete from table on which it's built.
[31 Aug 2007 8:07] Sergey Spatar
There's a mistake in the example above. In the test 2 table name must be m_10m, of course:

mysql> delete from m_10m;
Query OK, 10000000 rows affected (1 min 18.68 sec)
[25 Feb 2009 10:39] Sergey Vojtovich
Merge engine doesn't implement delete_all_rows() method, falling back to slow row-by-row delete. Generally it used to be a known limitation, but it seems we may fix it in 5.1 and up.
[26 Apr 2009 4:21] MySQL Verification Team
Thank you for the bug report.

c:\dbs>c:\dbs\5.0\bin\mysql -uroot --port=3500 --prompt="mysql 5.0 > "
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 102
Server version: 5.0.82-Win X64 revno 2778-log Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.0 > use test
Database changed
mysql 5.0 > select count(*) from t_10m;
+----------+
| count(*) |
+----------+
| 10000000 |
+----------+
1 row in set (0.00 sec)

mysql 5.0 > delete from t_10m;
Query OK, 10000000 rows affected (0.26 sec)

c:\dbs>c:\dbs\5.0\bin\mysql -uroot --port=3500 --prompt="mysql 5.0 > "
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 153
Server version: 5.0.82-Win X64 revno 2778-log Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.0 > use test
Database changed
mysql 5.0 > select count(*) from t_10m;
+----------+
| count(*) |
+----------+
| 10000000 |
+----------+
1 row in set (0.00 sec)

mysql 5.0 > create table m_10m (c1 varchar(100), c2 varchar(100)) engine=merge union=(t_10m);
Query OK, 0 rows affected (0.20 sec)

mysql 5.0 >
mysql 5.0 > delete from m_10m;
Query OK, 10000000 rows affected (8 min 43.67 sec)

mysql 5.0 >

c:\dbs>c:\dbs\5.1\bin\mysql -uroot --port=3510 --prompt="mysql 5.1 >"
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 52
Server version: 5.1.35-Win X64 revno: 2853-log Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.1 >use test
Database changed
mysql 5.1 >select count(*) from t_10m;
+----------+
| count(*) |
+----------+
| 10000000 |
+----------+
1 row in set (0.00 sec)

mysql 5.1 >create table m_10m (c1 varchar(100), c2 varchar(100)) engine=merge union=(t_10m);
Query OK, 0 rows affected (0.21 sec)

mysql 5.1 >
mysql 5.1 >delete from m_10m;
Query OK, 10000000 rows affected (8 min 32.45 sec)

mysql 5.1 >
[26 Apr 2009 4:23] MySQL Verification Team
Multi-threaded client for populate test table

Attachment: bug30743.cpp (text/plain), 3.25 KiB.