| Bug #36109 | index on part of varchar field is not used when it could be | ||
|---|---|---|---|
| Submitted: | 16 Apr 2008 5:12 | Modified: | 10 Jul 2009 9:39 |
| Reporter: | Ben Krug | Email Updates: | |
| Status: | No Feedback | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
| Version: | 5.0.44, 5.0.51, 5.0.66a, 5.1.26-rc | OS: | Any |
| Assigned to: | Assigned Account | CPU Architecture: | Any |
[9 Aug 2008 6:00]
Valeriy Kravchuk
Thank you for a bug report. Verified just as described with 5.0.66a:
C:\Program Files\MySQL\MySQL Server 5.0\bin>mysql -uroot -proot -P3308 test
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 17
Server version: 5.0.66a-enterprise-gpl-nt MySQL Enterprise Server (GPL)
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> create table bentest (
-> id int not null auto_increment,
-> stamptime timestamp default current_timestamp,
-> name varchar(10),
-> primary key (id),
-> key name_idx (stamptime,name(4))
-> ) engine=MyISAM;
Query OK, 0 rows affected (0.05 sec)
mysql> insert into bentest (name) values ('one');
Query OK, 1 row affected (0.02 sec)
mysql> insert into bentest (name) values ('two');
Query OK, 1 row affected (0.00 sec)
mysql> insert into bentest (name) values ('three');
Query OK, 1 row affected (0.00 sec)
mysql> insert into bentest (name) values ('four');
Query OK, 1 row affected (0.00 sec)
mysql> insert into bentest (name) values ('five');
Query OK, 1 row affected (0.00 sec)
mysql> insert into bentest (name) values ('six');
Query OK, 1 row affected (0.00 sec)
mysql> insert into bentest (name) values ('seven');
Query OK, 1 row affected (0.00 sec)
mysql> insert into bentest (name) values ('seven2');
Query OK, 1 row affected (0.00 sec)
mysql> insert into bentest (name) values ('seven3');
Query OK, 1 row affected (0.00 sec)
mysql> insert into bentest (name) values ('eight');
Query OK, 1 row affected (0.00 sec)
mysql> explain select count(name) from bentest where stamptime>'2007-01-01 00:00
:00' and
-> name='one';
+----+-------------+---------+------+---------------+------+---------+------+---
---+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | ro
ws | Extra |
+----+-------------+---------+------+---------------+------+---------+------+---
---+-------------+
| 1 | SIMPLE | bentest | ALL | name_idx | NULL | NULL | NULL |
10 | Using where |
+----+-------------+---------+------+---------------+------+---------+------+---
---+-------------+
1 row in set (0.05 sec)
Index can be used:
mysql> explain select count(name) from bentest force index(name_idx) where stamp
time>'2007-01-01 00:00:00' and name='one';
+----+-------------+---------+-------+---------------+----------+---------+-----
-+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref
| rows | Extra |
+----+-------------+---------+-------+---------------+----------+---------+-----
-+------+-------------+
| 1 | SIMPLE | bentest | range | name_idx | name_idx | 4 | NULL
| 10 | Using where |
+----+-------------+---------+-------+---------------+----------+---------+-----
-+------+-------------+
1 row in set (0.00 sec)
and it should be used.
[11 Aug 2008 4:59]
Valeriy Kravchuk
Same results (index is not used but can and should be used) on 5.1.26-rc.
[10 Jul 2009 23:00]
Bugs System
No feedback was provided for this bug for over a month, so it is being suspended automatically. If you are able to provide the information that was originally requested, please do so and change the status of the bug back to "Open".
Description: An compound index using the first n characters of a varchar field is not being used for an equality test with a string of (n-1) characters when done one way, but not when done another way. The index should suffice and be used. How to repeat: create table bentest ( id int not null auto_increment, stamptime timestamp default current_timestamp, name varchar(10), primary key (id), key name_idx (stamptime,name(4)) ) engine=MyISAM; insert into bentest (name) values ('one'); insert into bentest (name) values ('two'); insert into bentest (name) values ('three'); insert into bentest (name) values ('four'); insert into bentest (name) values ('five'); insert into bentest (name) values ('six'); insert into bentest (name) values ('seven'); insert into bentest (name) values ('seven2'); insert into bentest (name) values ('seven3'); insert into bentest (name) values ('eight'); explain select count(name) from bentest where stamptime>'2007-01-01 00:00:00' and name='one'; --index not used here, although it could/should be? +----+-------------+---------+------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------+------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | bentest | ALL | name_idx | NULL | NULL | NULL |10 | Using where | +----+-------------+---------+------+---------------+------+---------+------+------+-------------+ 1 row in set, 2 warnings (0.00 sec) alter table bentest drop key name_idx; alter table bentest add key name_idx (stamptime,name); explain select count(name) from bentest where stamptime>'2007-01-01 00:00:00' and name='one'; --index on full field is used +----+-------------+---------+-------+---------------+----------+---------+------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------+-------+---------------+----------+---------+------+------+--------------------------+ | 1 | SIMPLE | bentest | index | name_idx | name_idx | 37 | NULL | 10 | Using where; Using index | +----+-------------+---------+-------+---------------+----------+---------+-----+------+--------------------------+ 1 row in set, 2 warnings (0.03 sec) alter table bentest drop key name_idx; alter table bentest add key name_idx (name(4),stamptime); explain select count(name) from bentest where stamptime>'2007-01-01 00:00:00' and name='one'; --index using 4 characters if used if the order of the columns is switched +----+-------------+---------+-------+---------------+----------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------+-------+---------------+----------+---------+------+------+-------------+ | 1 | SIMPLE | bentest | range | name_idx | name_idx | 19 | NULL | 1 | Using where | +----+-------------+---------+-------+---------------+----------+---------+------+------+-------------+ 1 row in set, 2 warnings (0.02 sec)