| Bug #61778 | Select not using index, when data type mis-match - simple conversion is possible | ||
|---|---|---|---|
| Submitted: | 6 Jul 2011 21:09 | Modified: | 7 Jul 2011 6:16 |
| Reporter: | W Spikes | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
| Version: | 5.1.39 | OS: | Linux |
| Assigned to: | CPU Architecture: | Any | |
[7 Jul 2011 3:45]
Valeriy Kravchuk
Formally this is not a bug, as MySQL works as described in the manual, http://dev.mysql.com/doc/refman/5.1/en/type-conversion.html: "For comparisons of a string column with a number, MySQL cannot use an index on the column to look up the value quickly. If str_col is an indexed string column, the index cannot be used when performing the lookup in the following statement: SELECT * FROM tbl_name WHERE str_col=1; The reason for this is that there are many different strings that may convert to the value 1, such as '1', ' 1', or '1a'. " So, why not to quote value you compare string column to? Or why not to use explicit CAST()?
[7 Jul 2011 6:16]
W Spikes
I understand that going in reverse (atoi) is a bit more complex and has many variable circumstances, as you duly quoted. In the context you supplied, it's certainly understandable that it cannot be as easily optimized. However, going in the order of itoa is a rather primitive operation, and I fail to see how it's overly complex to optimize this instance. "Why not..." Well, typically my understanding is that prepre/execute does quote it automatically, which is great, but when testing commands manually... you don't really want a query on a large data set going unoptimized, and it's only human to sometimes forget the quotes.
[8 Jul 2011 5:31]
MySQL Verification Team
Newer versions of mysql 5.5 will tell you an index cannot be used if you say: EXPLAIN EXTENDED SELECT ... SHOW WARNINGS;
Description: A large table. PK column is VARCHAR(32). When executing a SELECT using a WHERE against the PK of that table, the index is correctly used when the value is quoted, but is not used when the value is left unquoted. This seems a bit crazy, since it's fairly cheap to sprintf(), to preform an itoa (not part of the standard C library) -- whereas it's rather expensive to attempt conversion of every column in the table to an integer. How to repeat: Unable to reproduce exactly, but the gist of it is outlined below (via EXPLAIN/DESC). mysql> desc table_a \G *************************** 1. row *************************** Field: column_a Type: varchar(32) Null: NO Key: PRI Default: Extra: *************************** 2. row *************************** [snip!] mysql> explain select * from table_a where column_a = '382957' \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: table_a type: const possible_keys: PRIMARY key: PRIMARY key_len: 34 ref: const rows: 1 Extra: 1 row in set (0.00 sec) mysql> explain select * from table_a where column_a = 382957 \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: table_a type: ALL possible_keys: PRIMARY key: NULL key_len: NULL ref: NULL rows: 939053 Extra: Using where 1 row in set (0.00 sec) mysql> show indexes from dh_id \G *************************** 1. row *************************** Table: table_a Non_unique: 0 Key_name: PRIMARY Seq_in_index: 1 Column_name: column_a Collation: A Cardinality: 759369 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: *************************** 2. row *************************** [snip!] Suggested fix: While I'm not that familiar with MySQL's code, I wouldn't imagine it'd be too difficult to sprintf() the value we're looking for, as a quick and dirty optimization technique.