Test case:
--disable_warnings DROP TABLE /*! IF EXISTS */ t1; DROP TABLE /*! IF EXISTS */ t2; --enable_warnings
CREATE TABLE t1 ( col_int_key int(11) DEFAULT NULL, col_varchar_10_latin1_key varchar(10) DEFAULT NULL, col_varchar_10_utf8_key varchar(10) CHARACTER SET utf8 DEFAULT NULL, KEY col_int_key (col_int_key), KEY col_varchar_10_latin1_key (col_varchar_10_latin1_key), KEY col_varchar_10_utf8_key (col_varchar_10_utf8_key) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; INSERT INTO t1 VALUES (650903552,'cmxffkpsel','z'),(535298048,'tvtjrcmxff','y'),(1626865664,'when','for'),(39649280,'rcvljitvtj','ercvljitvt'),(792068096,'ttercvljit','jttercvlji'); CREATE TABLE t2 ( col_varchar_10_utf8_key varchar(10) CHARACTER SET utf8 DEFAULT NULL, col_int_key int(11) DEFAULT NULL, col_varchar_10_latin1_key varchar(10) DEFAULT NULL, KEY col_varchar_10_utf8_key (col_varchar_10_utf8_key), KEY col_int_key (col_int_key), KEY col_varchar_10_latin1_key (col_varchar_10_latin1_key) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; INSERT INTO t2 VALUES ('g',7,'like'),('fujttercvl',6,'y'),('s',2,'e'),('didn\'t',0,'v'),('gvdrodpedk',8,'chogvdrodp'),('jichogvdro',7,'will');
SELECT t2 .col_int_key FROM t2 JOIN t1 ON t2 .col_varchar_10_latin1_key = t1 .col_varchar_10_utf8_key ; EXPLAIN SELECT t2 .col_int_key FROM t2 JOIN t1 ON t2 .col_varchar_10_latin1_key = t1 .col_varchar_10_utf8_key ;
SET SESSION join_cache_level = 4; SET SESSION join_buffer_size = 164;
Test case:
--disable_warnings
DROP TABLE /*! IF EXISTS */ t1;
DROP TABLE /*! IF EXISTS */ t2;
--enable_warnings
CREATE TABLE t1 ( 10_latin1_ key varchar(10) DEFAULT NULL, 10_utf8_ key varchar(10) CHARACTER SET utf8 DEFAULT NULL, 10_latin1_ key (col_varchar_ 10_latin1_ key), 10_utf8_ key (col_varchar_ 10_utf8_ key) 'cmxffkpsel' ,'z'),( 535298048, 'tvtjrcmxff' ,'y'),( 1626865664, 'when', 'for'), (39649280, 'rcvljitvtj' ,'ercvljitvt' ),(792068096, 'ttercvljit' ,'jttercvlji' ); 10_utf8_ key varchar(10) CHARACTER SET utf8 DEFAULT NULL, 10_latin1_ key varchar(10) DEFAULT NULL, 10_utf8_ key (col_varchar_ 10_utf8_ key), 10_latin1_ key (col_varchar_ 10_latin1_ key) 'like') ,('fujttercvl' ,6,'y') ,('s',2, 'e'),(' didn\'t' ,0,'v') ,('gvdrodpedk' ,8,'chogvdrodp' ),('jichogvdro' ,7,'will' );
col_int_key int(11) DEFAULT NULL,
col_varchar_
col_varchar_
KEY col_int_key (col_int_key),
KEY col_varchar_
KEY col_varchar_
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO t1 VALUES (650903552,
CREATE TABLE t2 (
col_varchar_
col_int_key int(11) DEFAULT NULL,
col_varchar_
KEY col_varchar_
KEY col_int_key (col_int_key),
KEY col_varchar_
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO t2 VALUES ('g',7,
SELECT t2 .col_int_key FROM t2 JOIN t1 ON t2 .col_varchar_ 10_latin1_ key = t1 .col_varchar_ 10_utf8_ key ; 10_latin1_ key = t1 .col_varchar_ 10_utf8_ key ;
EXPLAIN SELECT t2 .col_int_key FROM t2 JOIN t1 ON t2 .col_varchar_
SET SESSION join_cache_level = 4;
SET SESSION join_buffer_size = 164;
SELECT t2 .col_int_key FROM t2 JOIN t1 ON t2 .col_varchar_ 10_latin1_ key = t1 .col_varchar_ 10_utf8_ key ; 10_latin1_ key = t1 .col_varchar_ 10_utf8_ key ;
EXPLAIN SELECT t2 .col_int_key FROM t2 JOIN t1 ON t2 .col_varchar_