A mechanically-simplified testcase:
--disable_warnings drop table if exists t1, t2; --enable_warnings SET SESSION optimizer_use_mrr = 'force'; SET SESSION join_cache_level = 6; SET SESSION join_buffer_size = 1024; CREATE TABLE t1 ( pk int(11) NOT NULL AUTO_INCREMENT, col_varchar_1024_latin1_key varchar(1024) DEFAULT NULL, PRIMARY KEY (pk), KEY col_varchar_1024_latin1_key (col_varchar_1024_latin1_key) ) ENGINE=Aria;
INSERT INTO t1 VALUES (1,'z'), (2,'abcdefjhjkl'), (3,'in'), (4,'abcdefjhjkl'), (6,'abcdefjhjkl');
CREATE TABLE t2 ( col_varchar_10_latin1 varchar(10) DEFAULT NULL ) ENGINE=Aria; INSERT INTO t2 VALUES ('foo'), ('foo');
EXPLAIN SELECT count(*) FROM t1 AS table1, t2 AS table2 WHERE table1.col_varchar_1024_latin1_key = table2.col_varchar_10_latin1 AND table1.pk<>0 ;
SELECT count(*) FROM t1 AS table1, t2 AS table2 WHERE table1.col_varchar_1024_latin1_key = table2.col_varchar_10_latin1 AND table1.pk<>0 ;
A mechanically- simplified testcase:
--disable_warnings 1024_latin1_ key varchar(1024) DEFAULT NULL, 1024_latin1_ key (col_varchar_ 1024_latin1_ key)
drop table if exists t1, t2;
--enable_warnings
SET SESSION optimizer_use_mrr = 'force';
SET SESSION join_cache_level = 6;
SET SESSION join_buffer_size = 1024;
CREATE TABLE t1 (
pk int(11) NOT NULL AUTO_INCREMENT,
col_varchar_
PRIMARY KEY (pk),
KEY col_varchar_
) ENGINE=Aria;
INSERT INTO t1 VALUES
(1,'z'),
(2,'abcdefjhjkl'),
(3,'in'),
(4,'abcdefjhjkl'),
(6,'abcdefjhjkl');
CREATE TABLE t2 ( 10_latin1 varchar(10) DEFAULT NULL
col_varchar_
) ENGINE=Aria;
INSERT INTO t2 VALUES ('foo'), ('foo');
EXPLAIN SELECT count(*) col_varchar_ 1024_latin1_ key = table2. col_varchar_ 10_latin1 AND table1.pk<>0 ;
FROM t1 AS table1, t2 AS table2
WHERE
table1.
SELECT count(*) col_varchar_ 1024_latin1_ key = table2. col_varchar_ 10_latin1 AND table1.pk<>0 ;
FROM t1 AS table1, t2 AS table2
WHERE
table1.