| 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: | |
| Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
| Version: | 4.0, 4.1, 5.0, 5.1 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[21 Oct 2005 21:30]
MySQL Verification Team
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.
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.