Description:
I've been bitten by some longlasting bug, maybe this issue is already known and documented, but I haven't found that part neither in the docs nor the bug tracker, so maybe this issue is unknown :-)
In short, "SELECT LIKE" used with constant values in the LIKE-clause should
optimize the query in order not to scan for wildcard expressions and instead make e.g. use of indexes.
At least according to optimization.html#range-access-single-part, a BTREE index is used for LIKE optimization when the statement doesn't start with a wildcard; this is untrue for certain scenarios (outlined below)
How to repeat:
mysql> CREATE TABLE `foo` (
`id_message` int(11) NOT NULL auto_increment,
`bar` varchar(50) NOT NULL default '0',
PRIMARY KEY (`id_message`)
) TYPE=MyISAM;
mysql>insert into foo(bar) values ("a"),("b"),("c");
mysql>explain select * from forum where id_message=1;
=> the select statement is using the PRIMARY index.
mysql>explain select * from forum where id_message='1';
=> the select statement is using the PRIMARY index.
mysql>explain select * from forum where id_message like '1';
=> the select statement does NOT use the PRIMARY index, resulting in a full tablescan.
mysql> show indexes from forum\G
*************************** 1. row ***************************
Table: foo
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: id_message
Collation: A
Cardinality: 7
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
The table handler is using a BTREE index, so looking up for constant values shouldn't be that hard.
Problem has been verified in both 4.0.27 and 5.0.19.
Suggested fix:
If the "LIKE"-statement doesn't contain any wildcards, make it behave like a "="-statement - at least within the optimizer.