MySQL Bugs: #73777: DELETE query takes a long time and examines more rows than exist in the table

Bug #73777	DELETE query takes a long time and examines more rows than exist in the table
Submitted:	31 Aug 2014 11:00	Modified:	1 Sep 2014 16:00
Reporter:	Paul Tumakaev	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Memory storage engine	Severity:	S3 (Non-critical)
Version:	5.6, 5.6.21, 5.5.40	OS:	Linux
Assigned to:		CPU Architecture:	Any

Description:
Table struct:
CREATE TABLE `db_test_table` (
`bid` smallint(5) unsigned NOT NULL,
`pid` smallint(5) unsigned NOT NULL,
`regtime` bigint(20) unsigned NOT NULL,
`vid` mediumint(9) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`vid`),
KEY `idx` (`regtime`,`bid`) USING BTREE
) ENGINE=MEMORY AUTO_INCREMENT=142064 DEFAULT CHARSET=binary ROW_FORMAT=DYNAMIC;

Table Data Length: 2,20 MB (2 303 264)
Index Length: 7,89 MB (8 269 910)
Rows in table: 142063

Query: DELETE FROM `db_test_table` WHERE `regtime`!=1407760802 AND `bid`=2;

mysql-slow.log:
# Time: 140815 18:21:21
# User@Host: db_admin[db_admin] @ nntro [192.168.1.2] Id: 849
# Schema: db Last_errno: 0 Killed: 0
# Query_time: 65.480393 Lock_time: 0.000103 Rows_sent: 0 Rows_examined: 371240031 Rows_affected: 2775
# Bytes_sent: 13
# Profile_starting: 0.000049
Profile_starting_cpu: 0.000049
Profile_checking_permissions: 0.000006
Profile_checking_permissions_cpu: 0.000005
Profile_Opening_tables: 0.000023
Profile_Opening_tables_cpu: 0.000023
Profile_init: 0.000026
Profile_init_cpu: 0.000026
Profile_System_lock: 0.000065
Profile_System_lock_cpu: 0.000065
Profile_updating: 65.480188
Profile_updating_cpu: 65.449760
Profile_end: 0.000007
Profile_end_cpu: 0.000006
Profile_query_end: 0.000002
Profile_query_end_cpu: 0.000002
Profile_closing_tables: 0.000007
Profile_closing_tables_cpu: 0.000007
Profile_freeing_items: 0.000022
Profile_freeing_items_cpu: 0.000022
Profile_logging_slow_query: 0.000001
Profile_logging_slow_query_cpu: 0.000001
# Profile_total: 65.480396 Profile_total_cpu: 65.449965
SET timestamp=1408112481;
DELETE FROM `db_test_table` WHERE `regtime`!=1407760802 AND `bid`=2;

DELETE from 2,20 MB MEMORY table takes 65 seconds on Intel Pentium G3420 @ 3.20GHz

How to repeat:
Download table from http://rghost.ru/57781387 or https://yadi.sk/d/YBVT6qGtajD4D
and execute query: DELETE FROM `db_test_table` WHERE `regtime`!=1407760802 AND `bid`=2;

Table dump

Attachment: db_test_table.sql.gz (application/x-gzip, text), 412.74 KiB.

Testcase:
-------------
drop table if exists t1;
create table `t1` (`a` int,`b` int,`c` int, `d` int not null auto_increment,
primary key (`d`), key (`c`,`a`) using btree) engine=memory;
insert into t1(a,b,c) values (1,2,3),(3,2,1),(1,1,1),(2,2,2),(3,4,5);
insert into t1(a,b,c) select a.a,a.b,a.c from t1 a,t1 b,t1 c,t1 d,t1 e,t1 f;
truncate mysql.slow_log;
flush status;
set global slow_query_log=1;
set session long_query_time=0;
set global log_output='table';
delete from `t1` where `c`!=1 and `a`=2;
set global slow_query_log=0;
show status like '%handler%';
select * from mysql.slow_log\g

Hello Paul,

Thank you for the bug report.
Verified as described.

Thank you Shane for the simplified test case.
Changing category to memory since Innodb/MyISAM doesn't have this issue.

Thanks,
Umesh

On my server this bug repeats more than ten times a day. Is there ways to solve this problem by server configuration? Can I configure my server to kill requests, lasting more than 1 second?
Thanks,
Paul Tumakaev

Paul, try HASH index, it works for me