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
Last activity: May 15, 2025, 07:03 AM