MySQL Bugs: #30090: DELETE from MEMORY table with BTREE primary key extremely slow

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

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)

--------------------

It's worth noting it took some 16+ minutes to delete the last 8,000 of 650,000 rows when i tried this.

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.

Just wondering what the status of this bug is?  

Almost 3 months since last update...  

Thanks

The issue has been fixed by patch for bug #30590: "delete from memory
table with composite btree primary key".
Setting to "Duplicate".