I am trying to debug a slow query, and when I
EXPLAIN the SQL, I get a warning:
Warning: #1739 Cannot use ref access on index '[index name]' due to type or collation conversion on field '[field name]'
The query is quite complex, so I have made an example setup that gets the same warning, the SQL is below, as well as a db fiddle (although the db fiddle doesn't show the warning, so I don't know much help that is!).
The original query is
EXPLAIN SELECT * FROM `t1` LEFT JOIN `t2` ON `t2`.`ext_id`=`t1`.`id` WHERE 1;
I also tried
EXPLAIN SELECT * FROM `t1` LEFT JOIN `t2` ON `t2`.`ext_id`=CAST(`t1`.`id` AS CHAR) WHERE 1;
but that doesn't allow the index to be utilized, and still has the same warning.
Is there any way I can utilize the index in this
CREATE TABLE `t1` ( `id` int NOT NULL, `name` varchar(255) NOT NULL ) ENGINE=InnoDB; INSERT INTO `t1` (`id`, `name`) VALUES (1, 'n1'), (2, 'n2'); CREATE TABLE `t2` ( `id` int NOT NULL, `ext_id` varchar(255) NOT NULL, `position` varchar(255) NOT NULL ) ENGINE=InnoDB; INSERT INTO `t2` (`id`, `ext_id`, `position`) VALUES (1, '1', 'p1'), (2, '2', 'p2'); ALTER TABLE `t1` ADD UNIQUE KEY `id` (`id`); ALTER TABLE `t2` ADD KEY `ext_id` (`ext_id`) USING BTREE;
Edit: Using MySQL 5.7
varcharcould contain non-integers for entries unrelated to this query. My best bet, if it's not possible to join as per my example, is probably to create and populate a temporary table,
ALTERing the type of that field after creation/population, for when I need to run this query
ext_idneed to be