| Bug #43371 | wrong index is used (sometimes) in tables with multiple keys | ||
|---|---|---|---|
| Submitted: | 4 Mar 2009 14:09 | Modified: | 10 Jan 2013 11:02 |
| Reporter: | Jens Rieks | Email Updates: | |
| Status: | Won't fix | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S1 (Critical) |
| Version: | 5.0, 5.1.31, 5.1.33-bzr | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[4 Mar 2009 14:09]
Jens Rieks
[4 Mar 2009 14:46]
Valeriy Kravchuk
Thank you for the problem report. What exact versions, 5.0.x and 5.1.x, you had used?
[4 Mar 2009 15:25]
Jens Rieks
- mysqld Ver 5.0.32-Debian_7etch5 for pc-linux-gnu on i486 (Debian etch distribution) - mysqld Ver 5.1.31-community-log (SUSE Linux Enterprise Server 10 (x86_64)) Thank you!
[4 Mar 2009 18:57]
Valeriy Kravchuk
Verified with your test case on recent 5.1.33 from bzr: valeriy-kravchuks-macbook-pro:5.1 openxs$ bin/mysql -uroot test < bug43371.sql id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE test1 index stamp id_stamp 12 NULL 31 Using where; Using index id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE test1 index stamp id_stamp 12 NULL 31 Using where; Using index id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE test1 index stamp id_stamp 12 NULL 31 Using where; Using index id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE test1 index stamp id_stamp 12 NULL 31 Using where; Using index id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE test1 index stamp id_stamp 12 NULL 31 Using where; Using index id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE test1 index stamp id_stamp 12 NULL 31 Using where; Using index id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE test1 range stamp stamp 8 NULL 5 Using where id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE test1 range stamp stamp 8 NULL 4 Using where id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE test1 range stamp stamp 8 NULL 3 Using where id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE test1 range stamp stamp 8 NULL 2 Using where id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE test1 range stamp stamp 8 NULL 1 Using where id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE test1 range stamp stamp 8 NULL 1 Using where valeriy-kravchuks-macbook-pro:5.1 openxs$ bin/mysql -uroot test Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 4 Server version: 5.1.33-debug Source distribution Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> analyze table test1; +------------+---------+----------+----------+ | Table | Op | Msg_type | Msg_text | +------------+---------+----------+----------+ | test.test1 | analyze | status | OK | +------------+---------+----------+----------+ 1 row in set (0.00 sec) mysql> show create table test1\G *************************** 1. row *************************** Table: test1 Create Table: CREATE TABLE `test1` ( `id` int(10) NOT NULL, `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL, `stamp` datetime NOT NULL, `val1` int(10) NOT NULL, `txt1` varchar(255) COLLATE utf8_unicode_ci NOT NULL, `txt2` varchar(255) COLLATE utf8_unicode_ci NOT NULL, `val2` float NOT NULL, `val3` float NOT NULL, `kind` int(19) NOT NULL, KEY `kind` (`kind`), KEY `id_stamp` (`id`,`stamp`), KEY `stamp` (`stamp`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci 1 row in set (0.00 sec) mysql> explain SELECT id FROM test1 WHERE stamp >= '2007-01-25 00:00:00'; +----+-------------+-------+-------+---------------+----------+---------+------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+----------+---------+------+------+--------------------------+ | 1 | SIMPLE | test1 | index | stamp | id_stamp | 12 | NULL | 31 | Using where; Using index | +----+-------------+-------+-------+---------------+----------+---------+------+------+--------------------------+ 1 row in set (0.00 sec) mysql> explain SELECT id FROM test1 WHERE stamp >= '2007-01-25 00:00:00'\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: test1 type: index possible_keys: stamp key: id_stamp key_len: 12 ref: NULL rows: 31 Extra: Using where; Using index 1 row in set (0.00 sec) mysql> SELECT count(*) FROM test1 WHERE stamp >= '2007-01-25 00:00:00'; +----------+ | count(*) | +----------+ | 6 | +----------+ 1 row in set (0.00 sec) mysql> SELECT count(*) FROM test1; +----------+ | count(*) | +----------+ | 31 | +----------+ 1 row in set (0.00 sec) mysql> explain SELECT id FROM test1 force index(stamp) WHERE stamp >= '2007-01-25 00:00:00'\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: test1 type: range possible_keys: stamp key: stamp key_len: 8 ref: NULL rows: 6 Extra: Using where 1 row in set (0.01 sec) Same problem for MyISAM table also.
[10 Jan 2013 11:02]
Erlend Dahl
Internally supplied analysis: As stated in the bugpage we choose index 'id_stamp' over index 'stamp' for queries which return more rows. This is because, id_stamp is a covering inde x. So the advantage of picking index 'stamp' goes away in fetching the rows from the table as index 'stamp' is not covering. So for the given data, we do not feel that this is a bug.
