How can I utilize an index with a different type or collation in a LEFT JOIN in MySQL?

Question

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

Answer 1

In MySQL 5.7, you can create a generated column and add an index to it. The only difference between this an MySQL 8.0 is that 8.0 allows you to create an index on an expression without adding the generated column.

ALTER TABLE `t2`
  ADD COLUMN ext_id_int INT AS (CAST(ext_id AS SIGNED)),
  ADD KEY `ext_id` (`ext_id_int`);

(It's not necessary to specify USING BTREE because that's the only type of index InnoDB supports on an INT column.)

Then you join on that generated column, and it will use the index:

EXPLAIN SELECT * FROM `t1` LEFT JOIN `t2` 
  ON `t2`.`ext_id_int`=`t1`.`id` 
WHERE 1;

Given the very small example data you show, EXPLAIN will still show it doing a table-scan, but it will use the index if you have a more substantial dataset, or if you use an index hint to tell it a table-scan is to be avoided if possible:

EXPLAIN SELECT * FROM `t1` LEFT JOIN `t2` FORCE INDEX (ext_id) 
  ON `t2`.`ext_id_int`=`t1`.`id` 
WHERE 1;