Bug #30090 DELETE from MEMORY table with BTREE primary key extremely slow
Submitted: 27 Jul 2007 6:23 Modified: 4 Dec 2007 5:07
Reporter: Trent Lloyd Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Memory storage engine Severity:S3 (Non-critical)
Version:5.0.44 OS:Any
Assigned to: Assigned Account CPU Architecture:Any
Tags: bfsm_2007_08_02

[27 Jul 2007 6:23] Trent Lloyd
Description:
It seems that when deleting from a MEMORY table with a BTREE  primary key, it goes very slowly

Especially when compared to a HASH index, and a MyISAM table with BTREE.

Attached is some simple code to show this.. in one case with BTREE it takes over 3 minutes to delete 5,000 rows out of 100,000.. with hash table it takes 0.1 seconds.

The same delete with MyISAM also takes less than a second.

It seems to be much worse deleting say, 10,000 rows from the end of the table than the start.

How to repeat:
create table test_delete ( col1 int,primary key using btree(col1)) engine=memory;

delimiter //
drop PROCEDURE delete_test
//

CREATE PROCEDURE delete_test()
BEGIN
SET @pos = 0;
WHILE 1000000 > @pos DO
INSERT INTO test_delete
VALUES (
@pos
);

SET @pos = @pos +1;

END WHILE;
END;
//

delimiter ;
call delete_test;
Query OK, 0 rows affected (42.83 sec)

delete from test_delete where col1 >= 999000;
Query OK, 1000 rows affected (3 min 26.86 sec)

---

create table test_delete_hash ( col1 int,primary key using hash(col1)) engine=memory;

delimiter //
drop PROCEDURE delete_test_hash
//

CREATE PROCEDURE delete_test_hash()
BEGIN
SET @pos = 0;
WHILE 1000000 > @pos DO
INSERT INTO test_delete_hash
VALUES (
@pos
);

SET @pos = @pos +1;

END WHILE;
END;
//

delimiter ;
call delete_test_hash;
Query OK, 0 rows affected (41.33 sec)

delete from test_delete_hash where col1 >= 999000;
Query OK, 1000 rows affected (0.10 sec)

--------------------
[27 Jul 2007 6:43] Trent Lloyd
It's worth noting it took some 16+ minutes to delete the last 8,000 of 650,000 rows when i tried this.
[29 Aug 2007 6:43] Jeff C.
I experienced this same issue on 5.0.46.

My situation I was doing:

select left(toquery,2) as l,count(*) from t group by l;

insert into processing (toquery) select toquery from t where toquery like 'aa%';
delete from t where toquery like 'aa%'; 

.. then ab, then ac, ad, ae, af ..  and then all of a sudden it started to chug, going up to 3 minutes to delete 569 rows.

Strange, only experienced this recently.. I suspect somehow code got pushed that caused this.  I will try older versions to report back.
[3 Dec 2007 16:25] Jeff C.
Just wondering what the status of this bug is?  

Almost 3 months since last update...  

Thanks
[4 Dec 2007 5:07] Ramil Kalimullin
The issue has been fixed by patch for bug #30590: "delete from memory
table with composite btree primary key".
Setting to "Duplicate".