Bug #11245 LIKE doesn't return rows when used with index
Submitted: 10 Jun 2005 13:45 Modified: 10 Jun 2005 14:13
Reporter: Jakub Vrana (Candidate Quality Contributor) Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:4.1.11 OS:Microsoft Windows (Windows XP)
Assigned to: CPU Architecture:Any

[10 Jun 2005 13:45] Jakub Vrana
Description:
If there is an index on the column used on the left side of LIKE and this index is used (i.e. right side of LIKE starts with a constant string of length at least 5), MySQL doesn't return any rows.

Dropping index or using '%' at the beginning of right side of LIKE or using RLIKE solves the problem.

How to repeat:
CREATE TABLE jazyky (domena varchar(50), PRIMARY KEY (domena));
INSERT INTO jazyky VALUES ('www.a-prague.com'), ('www.a-praha.com');
SELECT * FROM jazyky WHERE domena LIKE 'www.a-%';
-- Empty set (0.00 sec)

EXPLAIN SELECT * FROM jazyky WHERE domena LIKE 'www.a-%';
           id: 1
  select_type: SIMPLE
        table: jazyky
         type: range
possible_keys: domena
          key: domena
      key_len: 50
          ref: NULL
         rows: 1
        Extra: Using where; Using index
[10 Jun 2005 14:13] Jorge del Conde
Hi!

I was unable to reproduce this bug using 4.1.13 from bk:

mysql> SELECT * FROM jazyky WHERE domena LIKE 'www.a-%';
+------------------+
| domena           |
+------------------+
| www.a-prague.com |
| www.a-praha.com  |
+------------------+
2 rows in set (0.00 sec)

mysql> EXPLAIN SELECT * FROM jazyky WHERE domena LIKE 'www.a-%';
+----+-------------+--------+-------+---------------+---------+---------+------+------+--------------------------+
| id | select_type | table  | type  | possible_keys | key     | key_len | ref  | rows | Extra                    |
+----+-------------+--------+-------+---------------+---------+---------+------+------+--------------------------+
|  1 | SIMPLE      | jazyky | range | PRIMARY       | PRIMARY |     150 | NULL |    1 | Using where; Using index |
+----+-------------+--------+-------+---------------+---------+---------+------+------+--------------------------+
1 row in set (0.00 sec)

mysql>