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 LEFT JOIN
?
https://www.db-fiddle.com/f/s89hwBtMSvcUnipKfadsuM/1
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
varchar
could 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,ALTER
ing the type of that field after creation/population, for when I need to run this queryext_id
need to beVARCHAR
?