| Bug #11346 | slow query due to datatype cast? | ||
|---|---|---|---|
| Submitted: | 15 Jun 2005 9:48 | Modified: | 16 Jul 2005 20:31 |
| Reporter: | Pascal Sonon | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
| Version: | 4.1.12 | OS: | Linux (Linux Fedora Core 2) |
| Assigned to: | CPU Architecture: | Any | |
[25 Jun 2005 16:36]
Jorge del Conde
I was unable to reproduce this bug in 5.0.9bk: mysql> explain select * from mytable where id_table like '1'; +----+-------------+---------+--------+---------------+------+---------+------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------+--------+---------------+------+---------+------+------+-------+ | 1 | SIMPLE | mytable | system | PRIMARY | NULL | NULL | NULL | 1 | | +----+-------------+---------+--------+---------------+------+---------+------+------+-------+ 1 row in set (0.00 sec) mysql> explain select * from mytable where id_table = '1'; +----+-------------+---------+--------+---------------+------+---------+------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------+--------+---------------+------+---------+------+------+-------+ | 1 | SIMPLE | mytable | system | PRIMARY | NULL | NULL | NULL | 1 | | +----+-------------+---------+--------+---------------+------+---------+------+------+-------+ 1 row in set (0.00 sec) mysql> explain select * from mytable where id_table = 1; +----+-------------+---------+--------+---------------+------+---------+------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------+--------+---------------+------+---------+------+------+-------+ | 1 | SIMPLE | mytable | system | PRIMARY | NULL | NULL | NULL | 1 | | +----+-------------+---------+--------+---------------+------+---------+------+------+-------+ 1 row in set (0.00 sec)
[25 Jun 2005 16:38]
Jorge del Conde
I was also unable to reproduce it in 4.1.13
[16 Jul 2005 20:31]
Sergei Golubchik
Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://www.mysql.com/documentation/ and the instructions on how to report a bug at http://bugs.mysql.com/how-to-report.php Additional info: Yes, there is a reason why integer values are not casted to string values before query execution and index selection. Because according to implicit casting that MySQL performs for comparison, when you compare a string with a number, they are compared as numbers. E.g. when all the following will evaluate to TRUE: 1 = "1" 1 = " 1" 1 = " +1" 1 = "000000001" etc. You see, if you cast an integer (1 in my examples) to a string, and perform a string comparison, you won't be able to keep all the above TRUE anymore.
Description: When primary key is VARBIN, a select like this one (select * from table where id = x, where x is a integer) doesn't use primary key index while the same select with quoted integer values uses the index as expected. Is there a reason why integer values are not casted to string values before query execution and index selection? How to repeat: CREATE DATABASE dbtest; CREATE TABLE `mytable` ( `id_table` VARBINARY( 5 ) NOT NULL , `name` VARCHAR( 20 ) , PRIMARY KEY ( `id_table` ) ) TYPE = MYISAM ; INSERT INTO `mytable` ( `id_table` , `name` ) VALUES ('1', 'sample description'); explain select * from mytable where id_table like '1'; explain select * from mytable where id_table = '1'; A query on this table containing many records is rather fast, explain select * from mytable where id_table = 1; This query is MUCH SLOWER because primary key index is not used. Suggested fix: Could integer values be casted to string value before executing query? or is there a reason why numerical values are not converted to string values.