|
This script:
DROP TABLE IF EXISTS t1;
|
CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET latin1, KEY(a)) ENGINE=MyISAM;
|
INSERT INTO t1 VALUES ('00:00:00');
|
INSERT INTO t1 VALUES ('00:00:01');
|
INSERT INTO t1 VALUES ('00:00:02');
|
INSERT INTO t1 VALUES ('00:00:03');
|
INSERT INTO t1 VALUES ('00:00:04');
|
INSERT INTO t1 VALUES ('00:00:05');
|
INSERT INTO t1 VALUES ('00:00:06');
|
INSERT INTO t1 VALUES ('00:00:07');
|
EXPLAIN SELECT * FROM t1 WHERE a LIKE '00:00:00';
|
returns
+------+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
|
| 1 | SIMPLE | t1 | range | a | a | 13 | NULL | 1 | Using where; Using index |
|
+------+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
|
The index is used to optimize the query.
Now if I change the EXPLAIN SELECT query to:
EXPLAIN SELECT * FROM t1 WHERE a LIKE TIME'00:00:00';
|
it does not use the index any longer:
+------+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
|
| 1 | SIMPLE | t1 | index | a | a | 13 | NULL | 8 | Using where; Using index |
|
+------+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
|
It happens because of this wrong condition in get_mm_leaf:
if (value->cmp_type() == TIME_RESULT && field->cmp_type() != TIME_RESULT)
|
goto end;
|
This condition is valid for the comparison operators (<,>,=, etc), but should not be checked for LIKE.
|