MySQL Bugs: #52762: like should be index optimised if starts with

Bug #52762	like should be index optimised if starts with _ rather than %
Submitted:	12 Apr 2010 10:49	Modified:	13 Apr 2010 14:58
Reporter:	ken sands	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S5 (Performance)
Version:	5.3	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	_, INDEX, like, match exactly one

Description:
like is understandably optimised if it does not begin with %, however the engine is not clever enough to at least use a prefix index when the like begins with .

if you have field like '..ABC' 
and a prefix index on field(5)
that index should be usable (if not ideal) rather than reading from the main table.

 

How to repeat:
create a prefix index, run a like using a . at the start of the like expression.

Suggested fix:
calculate if an index if big enough to be used for a like that begins with . or many .'s and even better add the ability to create an index ignoring a number of characters.

I think you mean '_', not '.':

C:\Program Files\MySQL\MySQL Server 5.1\bin>mysql -uroot -proot -P3310 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 110
Server version: 5.1.43-community-log MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create table ttext(id int primary key, c1 varchar(20));
Query OK, 0 rows affected (0.17 sec)

mysql> alter table ttext add key k1 (c1(5));
Query OK, 0 rows affected (0.30 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> insert into ttext values(1, 'abcd'), (2, 'ddddddd'), (3, 'fffff');
Query OK, 3 rows affected (0.09 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> explain select c1 from ttext where c1 like 'a%';
+----+-------------+-------+-------+---------------+------+---------+------+----
--+-------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | row
s | Extra       |
+----+-------------+-------+-------+---------------+------+---------+------+----
--+-------------+
|  1 | SIMPLE      | ttext | range | k1            | k1   | 18      | NULL |
1 | Using where |
+----+-------------+-------+-------+---------------+------+---------+------+----
--+-------------+
1 row in set (0.05 sec)

mysql> explain select c1 from ttext where c1 like '__fff';
+----+-------------+-------+------+---------------+------+---------+------+-----
-+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows
 | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+-----
-+-------------+
|  1 | SIMPLE      | ttext | ALL  | NULL          | NULL | NULL    | NULL |    3
 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+-----
-+-------------+
1 row in set (0.00 sec)

mysql> select c1 from ttext where c1 like '__fff';
+-------+
| c1    |
+-------+
| fffff |
+-------+
1 row in set (0.00 sec)

Am I right? 

This looks like a reasonable feature request to me.

Yes sorry I meant _ not . (it's the old jumping between many languages thing)

Your example exactly states my case, thanks for that (I'll try to be as comprehensive in future).