Bug #34753 Optimizer doesn't see Index on text field.

• Submitted: 21 Feb 2008 23:28
• Modified: 22 Feb 2008 4:28
• Reporter: Eugene Turkestanov
• Status: Can't repeat
• Category: MySQL Server: Optimizer
• Severity: S2 (Serious)
• Version: 5.0.22
• OS: Linux
• CPU Architecture: Any

[21 Feb 2008 23:28] Eugene Turkestanov

Description:
CREATE TABLE S (
  id int(11) NOT NULL auto_increment,
  sl text collate latin1_general_cs NOT NULL,
  ic int(11) NOT NULL default '0',
  sl_c int(11) NOT NULL default '0',
  PRIMARY KEY  (id),
  UNIQUE KEY sl (sl(255), ic, sl_c),
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_cs;

CREATE TABLE R (
  id bigint(20) NOT NULL auto_increment,
  path varchar(200) collate latin1_general_cs NOT NULL default '',
  au varchar(100) collate latin1_general_cs default NULL,
  PRIMARY KEY  (id),
  UNIQUE KEY path (path).

) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_cs;

Insert thouse tables. Then try to run:

select * from R r 
where r.path like 'something%' 
and not exists (select 1 from S s where s.sl = r.au);

It'll be running for ages. If you check indexes usage with EXPLAIN statement, you'll see that sl index doesn't work. I tryied to use FORCE INDEX(sl), but the result was the same. 
It works when you use varchar type instead of text for sl field in S table.

How to repeat:
Look at description.

[22 Feb 2008 4:28] Valeriy Kravchuk

Thank you for a problem report. I was not able to repeat the behaviour described with a newer 5.0.x version:

C:\Program Files\MySQL\MySQL Server 5.1\bin>mysql -uroot -proot -P3308 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.54-enterprise-gpl-nt-log MySQL Enterprise Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE TABLE S (
    ->   id int(11) NOT NULL auto_increment,
    ->   sl text collate latin1_general_cs NOT NULL,
    ->   ic int(11) NOT NULL default '0',
    ->   sl_c int(11) NOT NULL default '0',
    ->   PRIMARY KEY  (id),
    ->   UNIQUE KEY sl (sl(255), ic, sl_c)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_cs;
Query OK, 0 rows affected (0.53 sec)

mysql> CREATE TABLE R (
    ->   id bigint(20) NOT NULL auto_increment,
    ->   path varchar(200) collate latin1_general_cs NOT NULL default '',
    ->   au varchar(100) collate latin1_general_cs default NULL,
    ->   PRIMARY KEY  (id),
    ->   UNIQUE KEY path (path)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_cs;
Query OK, 0 rows affected (0.08 sec)

mysql> insert into R values (1, 'something1', 'value1');
Query OK, 1 row affected (0.05 sec)

mysql> insert into R values (2, 'something2', 'value2');
Query OK, 1 row affected (0.03 sec)

mysql> insert into R values (3, 'nothing', 'value3');
Query OK, 1 row affected (0.03 sec)

mysql> insert into S values (1, 'value2', 1, 1);
Query OK, 1 row affected (0.39 sec)

mysql> insert into S values (2, 'irrelevant', 2, 2);
Query OK, 1 row affected (0.03 sec)

mysql> insert into S values (3, 'irrelevant again', 3, 3);
Query OK, 1 row affected (0.03 sec)

mysql> select * from R r
    -> where r.path like 'something%'
    -> and not exists (select 1 from S s where s.sl = r.au);
+----+------------+--------+
| id | path       | au     |
+----+------------+--------+
|  1 | something1 | value1 |
+----+------------+--------+
1 row in set (0.05 sec)

mysql> explain select * from R r
    -> where r.path like 'something%'
    -> and not exists (select 1 from S s where s.sl = r.au)\G
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: r
         type: range
possible_keys: path
          key: path
      key_len: 202
          ref: NULL
         rows: 1
        Extra: Using where
*************************** 2. row ***************************
           id: 2
  select_type: DEPENDENT SUBQUERY
        table: s
         type: ref
possible_keys: sl
          key: sl
      key_len: 257
          ref: test.r.au
         rows: 1
        Extra: Using where
2 rows in set (0.00 sec)

So, as you can see, index on sl is used. Please, try with the newer version, 5.0.51a, and in case of the same problem provide a more detailed explanation on how to repeat.