Bug #24183 optimizer misoptimize "LIKE" when using constant values
Submitted: 10 Nov 2006 11:53 Modified: 10 Nov 2006 14:12
Reporter: Anders Henke Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S4 (Feature request)
Version:5.0.30-BK, 4.0.27 and 5.0.19 OS:Linux (Linux)
Assigned to: CPU Architecture:Any

[10 Nov 2006 11:53] Anders Henke
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.
[10 Nov 2006 14:12] Valeriy Kravchuk
Thank you for a problem report. Verified just as described with 5.0.30-BK on Linux.

I think, this is a valid feature request for our optimizer to handle queries like this one in a more smart way (using "range" access method, at least) that full table scan. 

I can not call it a bug, as, likely, using LIKE forces conversion of your column to character type, and, hence, index can not be used.