Description:
mysql> CREATE TABLE `t` (
-> `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
-> `txt` char(50) NOT NULL,
-> PRIMARY KEY (`id`),
-> KEY `idx` (`txt`)
-> ) ENGINE=InnoDB AUTO_INCREMENT=26 DEFAULT CHARSET=utf8 ;
Query OK, 0 rows affected (0.22 sec)
mysql> explain extended select id from t where txt like '%';
+----+-------------+-------+-------+---------------+------+---------+------+------+----------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+-------+---------------+------+---------+------+------+----------+--------------------------+
| 1 | SIMPLE | t | index | NULL | idx | 150 | NULL | 1 | 100.00 | Using where; Using index |
+----+-------------+-------+-------+---------------+------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.03 sec)
mysql> show warnings;
+-------+------+----------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+----------------------------------------------------------------------------------+
| Note | 1003 | select `test`.`t`.`id` AS `id` from `test`.`t` where (`test`.`t`.`txt` like '%') |
+-------+------+----------------------------------------------------------------------------------+
1 row in set (0.00 sec)
How to repeat:
see above
Suggested fix:
For NOT NULL columns, the expression "where NOT_NULL_COL like '%'" should be optimized away like 1=1. This expression will always evaluate to true, because even empty string will match a like '%'
Description: mysql> CREATE TABLE `t` ( -> `id` int(10) unsigned NOT NULL AUTO_INCREMENT, -> `txt` char(50) NOT NULL, -> PRIMARY KEY (`id`), -> KEY `idx` (`txt`) -> ) ENGINE=InnoDB AUTO_INCREMENT=26 DEFAULT CHARSET=utf8 ; Query OK, 0 rows affected (0.22 sec) mysql> explain extended select id from t where txt like '%'; +----+-------------+-------+-------+---------------+------+---------+------+------+----------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+-------+---------------+------+---------+------+------+----------+--------------------------+ | 1 | SIMPLE | t | index | NULL | idx | 150 | NULL | 1 | 100.00 | Using where; Using index | +----+-------------+-------+-------+---------------+------+---------+------+------+----------+--------------------------+ 1 row in set, 1 warning (0.03 sec) mysql> show warnings; +-------+------+----------------------------------------------------------------------------------+ | Level | Code | Message | +-------+------+----------------------------------------------------------------------------------+ | Note | 1003 | select `test`.`t`.`id` AS `id` from `test`.`t` where (`test`.`t`.`txt` like '%') | +-------+------+----------------------------------------------------------------------------------+ 1 row in set (0.00 sec) How to repeat: see above Suggested fix: For NOT NULL columns, the expression "where NOT_NULL_COL like '%'" should be optimized away like 1=1. This expression will always evaluate to true, because even empty string will match a like '%'