Bug #14220 Optimizer does not use index when using numeric lookup on varchar field (MyISAM)
Submitted: 21 Oct 2005 21:00 Modified: 15 Nov 2017 18:10
Reporter: Gleb Paharenko Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:4.0, 4.1, 5.0, 5.1 OS:Any
Assigned to: CPU Architecture:Any
Triage: Triaged: D3 (Medium)

[21 Oct 2005 21:00] Gleb Paharenko
Description:
I've a weird behavior of an optimizer. In WHERE clause I'm trying to compare varchar field
with an integer. And EXPLAIN produces different outputs if my table has a primary key.

How to repeat:
CREATE TABLE ict (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
                  vcf VARCHAR(30),
                  INDEX (vcf)
                 );

INSERT INTO ict (vcf)
VALUES ('1'),('2'),('3'),('4'),('5'),('6'),('7'),('8'),('9'),('10'),
       ('11'),('12'),('13'),('14'),('15'),('16'),('17'),('18'),('19'),('20'); 

mysql> EXPLAIN SELECT * FROM ict WHERE vcf = 17\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: ict
         type: ALL
possible_keys: vcf
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 20
        Extra: Using where

--You see - it doesn't use index

mysql> alter table ict drop id;
Query OK, 20 rows affected (0.03 sec)
Records: 20  Duplicates: 0  Warnings: 0
mysql> EXPLAIN SELECT * FROM ict WHERE vcf = 17\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: ict
         type: index
possible_keys: vcf
          key: vcf
      key_len: 91
          ref: NULL
         rows: 20
        Extra: Using where; Using index
1 row in set (0.00 sec)

--Now it uses!!

Suggested fix:
Fix so the optimizer has the same behavior.
[21 Oct 2005 21:30] Miguel Solorzano
miguel@hegel:~/dbs/5.0> bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.16-debug

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

mysql> CREATE TABLE ict (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ->                   vcf VARCHAR(30),
    ->                   INDEX (vcf)
    ->                  );
Query OK, 0 rows affected (0.27 sec)

mysql> 
mysql> INSERT INTO ict (vcf)
    -> VALUES ('1'),('2'),('3'),('4'),('5'),('6'),('7'),('8'),('9'),('10'),
    ->        ('11'),('12'),('13'),('14'),('15'),('16'),('17'),('18'),('19'),('20'); 
Query OK, 20 rows affected (0.01 sec)
Records: 20  Duplicates: 0  Warnings: 0

mysql> EXPLAIN SELECT * FROM ict WHERE vcf = 17\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: ict
         type: ALL
possible_keys: vcf
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 20
        Extra: Using where
1 row in set (0.00 sec)

ERROR: 
No query specified

mysql> alter table ict drop id;
Query OK, 20 rows affected (0.04 sec)
Records: 20  Duplicates: 0  Warnings: 0

mysql> EXPLAIN SELECT * FROM ict WHERE vcf = 17\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: ict
         type: index
possible_keys: vcf
          key: vcf
      key_len: 33
          ref: NULL
         rows: 20
        Extra: Using where; Using index
1 row in set (0.01 sec)
[24 Oct 2005 3:31] Michael Stassen
I do not believe this is a bug.  The difference is not the presence or absence of the id field in the table, it is the presence or absence of the id field in the SELECT.  In the first case, 

 SELECT *

is equivalent to 

  SELECT id, vcf

while in the second case it is equivalent to 

  SELECT vcf

because column id has been dropped.  The index on vcf is a covering index in the latter case, but not the former.  Looking carefully at the EXPLAIN output, you will notice that all 20 rows are still being examined.  Indeed, you get the same result without dropping the id column if you leave it out of the SELECT:

mysql> SHOW CREATE TABLE ict\G
*************************** 1. row ***************************
       Table: ict
Create Table: CREATE TABLE `ict` (
  `id` int(11) NOT NULL auto_increment,
  `vcf` varchar(30) default NULL,
  PRIMARY KEY  (`id`),
  KEY `vcf` (`vcf`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT vcf FROM ict WHERE vcf = 17\G
*************************** 1. row ***************************
                 id: 1
    select_type: SIMPLE
             table: ict
              type: index
possible_keys: vcf
               key: vcf
         key_len: 31
               ref: NULL
             rows: 20
            Extra: Using where; Using index
1 row in set (0.00 sec)
[25 Jan 2010 3:14] Roel Van de Paar
This is unrelated to a covering index existing or not.

Proof:

mysql> SHOW CREATE TABLE ict\G EXPLAIN SELECT * FROM ict WHERE vcf = "17"\G
*************************** 1. row ***************************
       Table: ict
Create Table: CREATE TABLE `ict` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `vcf` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `vcf` (`vcf`)
) ENGINE=MyISAM AUTO_INCREMENT=21 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: ict
         type: ref
possible_keys: vcf
          key: vcf
      key_len: 33
          ref: const
         rows: 1
        Extra: Using where
1 row in set (0.00 sec)

Also see bug #50576

This may be another issue then the one described there. As such, I am re-opening this bug.
[25 Jan 2010 3:39] Roel Van de Paar
Verifying as D3. Issue is only repeatable on MyISAM. InnoDB sees the key.

New testcase:

DROP TABLE IF EXISTS ict;
CREATE TABLE ict (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,vcf VARCHAR(30),INDEX (vcf)) Engine=MyISAM;
INSERT INTO ict (vcf)
VALUES ('1'),('2'),('3'),('4'),('5'),('6'),('7'),('8'),('9'),('10'),('11'),('12'),('13'),('14'),('15'),('16'),('17'),('18'),('19'),('20'); 
EXPLAIN EXTENDED SELECT * FROM ict WHERE vcf = 17;
ALTER TABLE ict Engine=InnoDB;
EXPLAIN EXTENDED SELECT * FROM ict WHERE vcf = 17;
ALTER TABLE ict Engine=MyISAM;
ALTER TABLE ict DROP id;
EXPLAIN EXTENDED SELECT * FROM ict WHERE vcf = 17;

Result:

---------
mysql> EXPLAIN EXTENDED SELECT * FROM ict WHERE vcf = 17;
+----+-------------+-------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | ict   | ALL  | vcf           | NULL | NULL    | NULL |   20 |   100.00 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> ALTER TABLE ict Engine=InnoDB;
Query OK, 20 rows affected (0.15 sec)
Records: 20  Duplicates: 0  Warnings: 0

mysql> EXPLAIN EXTENDED SELECT * FROM ict WHERE vcf = 17;
+----+-------------+-------+-------+---------------+------+---------+------+------+----------+--------------------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-------+-------+---------------+------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | ict   | index | vcf           | vcf  | 33      | NULL |   20 |   100.00 | Using where; Using index |
+----+-------------+-------+-------+---------------+------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> ALTER TABLE ict Engine=MyISAM;
Query OK, 20 rows affected (0.11 sec)
Records: 20  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE ict DROP id;
Query OK, 20 rows affected (0.11 sec)
Records: 20  Duplicates: 0  Warnings: 0

mysql> EXPLAIN EXTENDED SELECT * FROM ict WHERE vcf = 17;
+----+-------------+-------+-------+---------------+------+---------+------+------+----------+--------------------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-------+-------+---------------+------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | ict   | index | vcf           | vcf  | 33      | NULL |   20 |   100.00 | Using where; Using index |
+----+-------------+-------+-------+---------------+------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)
---------

Very interesting when looked at in combination with bug #50576
[15 Apr 2010 17:23] Larry Adams
I can verify the description of the last poster.  Here is what I know about the bug.

1) It does not happen on InnoDB
2) If you run the explain, and it shows nulls for both possible_keys, and key, you have the issue.
3) If you simply perform an "alter table `suchandsuch`, engine=myisam;" and then simply rerun the explain, it shows not only the possible indexes, but also selects a viable index.
4) In my test case with Cacti, when a table looses it's mind as documented in the prior posts, the query time goes from < 1 second, to > 300 seconds.
5) Again, to fix the problem, a simple alter statement fixes it.
6) The problem will re-appear after several hours of continual operation.

Another interesting note is that with RHEL5.x (5.0.45.x) the problem happens, also with Solaris10 (5.0.88/89), the problem happens, on RHEL4.8 (CentOS) with 5.0.82b the problem is, or has not been, repeatable to date.

TheWitness
[15 Apr 2010 17:25] Larry Adams
And, based upon the following comment:

5) Again, to fix the problem, a simple alter statement fixes it.

I would request that this be reclassified as a S1.

TheWitness
[14 Nov 2017 13:18] Federico Razzoli
Still in 8.0.3.
[15 Nov 2017 18:10] Erlend Dahl
Posted by developer:

[14 Nov 2017 6:04] Roy Lyseng 

This is equally less of a bug as it was back in 2005 when it was first
closed.
[15 Nov 2017 22:32] Federico Razzoli
The last comment asks to reclassify the bug, so there is no explainaton of why this shouldn't be a bug. The motivation is also quite strange: support engineers can ask customers to run an ALTER TABLE to solve the problem and this will work, but in real world real people run into real problems. After the problem arose and caused damages for some time, we can find this funny workaround, yes, so reclassification is somewhat understandable.
[16 Nov 2017 7:45] Roy Lyseng
There's no reason to reclassify this bug, since the first analysis was
correct.
When providing an integer value against a character column in a predicate,
MySQL does not support indexed lookups, since the index requires a character
string.
The confusion probably comes from "Using index", which means a covering index
is being used to access data, when this is quicker than accessing data from
the base table directly. Since Innodb includes primary key in secondary key,
it can use covering indexes more often than MyISAM, that may also explain
a confusion. Removing the id column from a MyISAM table will "fix" this
problem,
as the query can be run on a covering index.