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: | |
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
[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.