Sample Header Ad - 728x90

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

1 vote
1 answer
2212 views
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
Asked by Ben Holness (183 rep)
Mar 28, 2023, 03:40 AM
Last activity: May 15, 2025, 07:03 AM