MySQL Bugs: #44445: Problem using index when selecting all columns

Bug #44445	Problem using index when selecting all columns
Submitted:	23 Apr 2009 19:24	Modified:	19 Aug 2009 8:52
Reporter:	Kiriakos Tsourapas	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	5.1.31-log, 5.0, 5.1, azalea bzr	OS:	Linux
Assigned to:		CPU Architecture:	Any
Tags:	BKA

Description:
The table test has a blob field which is not being used, so it is empty.

It has a primary key (id, msisdn, state, timeperformed)
It has another index (timeperformed, state)
It has some more columns, some indexed with other indexes and some not. The total columns are 8.

The following query takes forever (I could not wait for it to finish because the system was live and it had locked up all resources. I waited for about 1 minute and still had not finished).

select * from test where state = '1' and timeperformed < '2009-04-13 15:57:45' limit 1

while this one returns instantly :
SELECT * FROM test where id in (
select id from test where state = '1' and timeperformed < '2009-04-13 15:57:45'
) limit 1

In the first query, there seems to be a problem when MySQL tries to do the additional seek to fetch the data. 

In the second query, the subquery only uses indexed columns and returns instantly, and the parent query returns instantly too.

I know I am using an old MySQL version. If this is a known issue and has been fixed, please let me know. I could not find anything in the bugs database.

Thank you

How to repeat:
Only happens to live system with about 120.000 rows of data.

Thank you for the bug report. Yes you are using a quite older server version and our procedure to verify a bug is with the current source and lasted released version so would be nice if you try to test with latest released version on test environment and if the issue is still observed then could you please provide the dump file of the offended table (create table plus insert data statements) and your my.cnf file if for purposes test is relevant. Thanks in advance.

Hi again,

I have imported the data to version 5.1.31
The problem persists.

Here is the table :
----------------------------------
DROP TABLE IF EXISTS `test`;
CREATE TABLE `test` (
  `id` bigint(20) unsigned NOT NULL DEFAULT '0',
  `msisdn` varchar(20) NOT NULL,
  `state` varchar(5) NOT NULL,
  `timeperformed` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `blob_id` bigint(20) unsigned NOT NULL,
  `caller` varchar(20) NOT NULL,
  `media` longblob NOT NULL,
  `duration` double NOT NULL,
  PRIMARY KEY (`id`,`msisdn`,`state`,`timeperformed`) USING BTREE,
  KEY `blob_idx` (`blob_id`),
  KEY `idx_Main` (`msisdn`,`state`,`timeperformed`,`caller`),
  KEY `idx_Reverse` (`caller`,`timeperformed`,`state`,`msisdn`),
  KEY `cleanerIdx` (`timeperformed`,`state`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
----------------------------------

There are two problems :

Problem #1 :
The following select takes 30 seconds to complete :
select id, caller from voicemessages where timeperformed < '2009-04-15 15:57:45' and state = '1' limit 1

while if I try without the caller, it returns immediately :
select id from voicemessages where timeperformed < '2009-04-15 15:57:45' and state = '1' limit 1

Please find attached the logs showing the time it takes to execute the queries and the explain of each statement.

Problem #2:
The following statement, returns in completely different times, depending where I execute it from. It returns instantly from linux mysql command line, but takes 45 seconds from MySQL Browser. Both command line and browser are connected with LAN to the server.
select id, blob_id from voicemessages where timeperformed < '2009-04-15 15:57:45' and state = '1' limit 1;

I included the explain of this statement in the logs.

Logs showing explain statement and times of execution

Attachment: MySQLBug44445.log (application/octet-stream, text), 1.93 KiB.

I re-post the "show create" of the table. One of the indexes was wrong in the previous submission. This is the one where the tests were performed

DROP TABLE IF EXISTS `voice2sms`.`voicemessages`;
CREATE TABLE  `voice2sms`.`voicemessages` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `msisdn` varchar(20) NOT NULL,
  `state` varchar(5) NOT NULL,
  `timeperformed` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `blob_id` bigint(20) unsigned NOT NULL,
  `caller` varchar(20) NOT NULL,
  `media` longblob NOT NULL,
  `duration` double NOT NULL,
  PRIMARY KEY (`id`,`msisdn`,`state`,`timeperformed`) USING BTREE,
  KEY `blob_idx` (`blob_id`),
  KEY `idx_Main` (`msisdn`,`state`,`timeperformed`,`caller`),
  KEY `idx_Reverse` (`caller`,`timeperformed`,`state`,`msisdn`),
  KEY `cleanerIdx` (`timeperformed`,`state`,`id`,`blob_id`) USING BTREE
) ENGINE=MyISAM AUTO_INCREMENT=1415518 DEFAULT CHARSET=latin1;

Thank you in advance for you help.

Thank you for the report.

Verified as described:

mysql> select * from voicemessages where timeperformed < '2009-09-15 15:57:45' and state = '1' limit 1;
+-----+---------------------+-------+---------------------+---------+--------+-------+------------+
| id  | msisdn              | state | timeperformed       | blob_id | caller | media | duration   |
+-----+---------------------+-------+---------------------+---------+--------+-------+------------+
| 121 | f the Embedded MySQ | 1     | 2009-08-14 17:04:11 |     120 | e      |       | -859765344 |
+-----+---------------------+-------+---------------------+---------+--------+-------+------------+
1 row in set (13.73 sec)

mysql> select id from voicemessages where timeperformed < '2009-09-15 15:57:45' and state = '1' limit 1;
+-----+
| id  |
+-----+
| 121 |
+-----+
1 row in set (2.95 sec)

mysql> select * from voicemessages where id in (select id from voicemessages where timeperformed < '2009-09-15 15:57:45' and state = '1') limit 1;
+-----+---------------------+-------+---------------------+---------+--------+-------+------------+
| id  | msisdn              | state | timeperformed       | blob_id | caller | media | duration   |
+-----+---------------------+-------+---------------------+---------+--------+-------+------------+
| 121 | f the Embedded MySQ | 1     | 2009-08-14 17:04:11 |     120 | e      |       | -859765344 |
+-----+---------------------+-------+---------------------+---------+--------+-------+------------+
1 row in set (0.31 sec)

mysql> explain select * from voicemessages where id in (select id from voicemessages where timeperformed < '2009-09-15 15:57:45' and state = '1') limit 1;
+----+--------------------+---------------+----------------+--------------------+---------+---------+------+---------+--------------------------+
| id | select_type        | table         | type           | possible_keys      | key     | key_len | ref  | rows    | Extra                    |
+----+--------------------+---------------+----------------+--------------------+---------+---------+------+---------+--------------------------+
|  1 | PRIMARY            | voicemessages | ALL            | NULL               | NULL    | NULL    | NULL | 1024000 | Using where              |
|  2 | DEPENDENT SUBQUERY | voicemessages | index_subquery | PRIMARY,cleanerIdx | PRIMARY | 8       | func |   10240 | Using index; Using where |
+----+--------------------+---------------+----------------+--------------------+---------+---------+------+---------+--------------------------+
2 rows in set (0.01 sec)

Since version 5.4.4 workaround does not work:

mysql> select * from voicemessages where id in (select id from voicemessages where timeperformed < '2009-09-15 15:57:45' and state = '1') limit 1;
+-----+---------------------+-------+---------------------+---------+--------+-------+------------+
| id  | msisdn              | state | timeperformed       | blob_id | caller | media | duration   |
+-----+---------------------+-------+---------------------+---------+--------+-------+------------+
| 121 | f the Embedded MySQ | 1     | 2009-08-14 13:04:11 |     120 | e      |       | -859765344 |
+-----+---------------------+-------+---------------------+---------+--------+-------+------------+
1 row in set (8.33 sec)

mysql> explain select * from voicemessages where id in (select id from voicemessages where timeperformed < '2009-09-15 15:57:45' and state = '1') limit 1;
+----+-------------+---------------+-------+--------------------+------------+---------+------+---------+---------------------------------------+
| id | select_type | table         | type  | possible_keys      | key        | key_len | ref  | rows    | Extra                                 |
+----+-------------+---------------+-------+--------------------+------------+---------+------+---------+---------------------------------------+
|  1 | PRIMARY     | voicemessages | ALL   | PRIMARY            | NULL       | NULL    | NULL | 1024000 |                                       |
|  1 | PRIMARY     | voicemessages | range | PRIMARY,cleanerIdx | cleanerIdx | 4       | NULL | 1023999 | Using where; Using index; Materialize |
+----+-------------+---------------+-------+--------------------+------------+---------+------+---------+---------------------------------------+
2 rows in set (0.01 sec)

Same results with BKA