A customer reported last month that MySQL does a full table scan
(FTS) if a query was filtered by a INT value on a
VARCHAR column. First I told him that this is not
true any more because MySQL has fixed this behaviour long time
ago. He showed me that I was wrong:
CREATE TABLE `test` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`data` varchar(64) DEFAULT NULL,
`ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `data` (`data`)
) ENGINE=InnoDB;
EXPLAIN SELECT * FROM test WHERE data = 42\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: test
partitions: NULL
type: ALL
possible_keys: data
key: NULL
key_len: NULL
ref: NULL
rows: 522500
filtered: 10.00
Extra: Using where
EXPLAIN SELECT * FROM test WHERE data = …[Read more]
