Test case:
SET SESSION optimizer_use_mrr = 'force'; SET SESSION join_cache_level = 6; SET SESSION join_buffer_size = 1024;
--disable_warnings DROP TABLE /*! IF EXISTS */ t1; DROP TABLE /*! IF EXISTS */ t2; --enable_warnings
CREATE TABLE t1 ( col_varchar_10_latin1_key varchar(10) DEFAULT NULL, pk int(11) NOT NULL AUTO_INCREMENT, col_varchar_10_latin1 varchar(10) DEFAULT NULL, col_varchar_1024_latin1_key varchar(1024) DEFAULT NULL, col_varchar_10_utf8 varchar(10) CHARACTER SET utf8 DEFAULT NULL, PRIMARY KEY (pk), KEY col_varchar_10_latin1_key (col_varchar_10_latin1_key), KEY col_varchar_1024_latin1_key (col_varchar_1024_latin1_key) ) ENGINE=Aria AUTO_INCREMENT=51 DEFAULT CHARSET=latin1 PAGE_CHECKSUM=1; INSERT INTO t1 VALUES ('FSLXI',1,'mean','z','CETCI'),('r',2,'abcdefjhjkl','abcdefjhjkl','g'),('been',3,'abcdefjhjkl','in','hey'),('I\'ll',4,'abcdefjhjkl','abcdefjhjkl','CVCZR'),('GSMTS',6,'he\'s','abcdefjhjkl','u'),('GSKUD',7,'h','at','the'),('going',8,'this','RNEVG','JVXGC'),('say',9,'WGBVU','ZNQVR','SOFFU'),('you\'re',10,'z','j','tell'),('abcdefjhjkl',14,'abcdefjhjkl','no','p'),('NOFJH',15,'GLSXZ','k','DWTZJ'),('b',16,'could','JFJAU','LIPLE'),('abcdefjhjkl',17,'z','f','IGFKY'),('abcdefjhjkl',18,'abcdefjhjkl','g','e'),('didn\'t',5,'this','this','would'),('AUWVN',12,'abcdefjhjkl','AOOUK','EHGSK'),('QNHWK',20,'q','on','m'),('x',22,'n','it','abcdefjhjkl'),('mean',23,'KVJEK','have','right'),('h',24,'VJVDS','abcdefjhjkl','XJJVP'),('if',26,'u','abcdefjhjkl','CLULE'),('abcdefjhjkl',28,'you\'re','abcdefjhjkl','think'),('with',30,'z','on','n'),('s',31,'s','MUVHW','a'),('abcdefjhjkl',33,'LCVTC','she','abcdefjhjkl'),('m',19,'for','GNCJI','abcdefjhjkl'),('abcdefjhjkl',11,'up','all','it\'s'),('g',21,'u','abcdefjhjkl','then'),('t',25,'abcdefjhjkl','MQFEC','right'),('out',34,'abcdefjhjkl','g','GRPDG'),('your',35,'can\'t','RXSIK','b'),('b',37,'abcdefjhjkl','FZCZL','d'),('to',42,'m','will','l'),('abcdefjhjkl',43,'abcdefjhjkl','TNOSX','PWQKO');
CREATE TABLE t2 ( col_varchar_10_utf8 varchar(10) CHARACTER SET utf8 DEFAULT NULL, col_varchar_10_latin1 varchar(10) DEFAULT NULL, col_varchar_10_latin1_key varchar(10) DEFAULT NULL, pk int(11) NOT NULL AUTO_INCREMENT, col_varchar_1024_latin1_key varchar(1024) DEFAULT NULL, PRIMARY KEY (pk), KEY col_varchar_10_latin1_key (col_varchar_10_latin1_key), KEY col_varchar_1024_latin1_key (col_varchar_1024_latin1_key) ) ENGINE=Aria AUTO_INCREMENT=51 DEFAULT CHARSET=latin1 PAGE_CHECKSUM=1; INSERT INTO t2 VALUES ('GINHU','abcdefjhjkl','c',1,'i'),('m','abcdefjhjkl','ILVHS',2,'abcdefjhjkl'),('he\'s','abcdefjhjkl','were',3,'PRNZK'),('d','abcdefjhjkl','here',4,'abcdefjhjkl'),('can','BLJCV','go',5,'that'),('this','SWGQI','BFBWJ',6,'QECYF'),('time','p','m',7,'q'),('abcdefjhjkl','is','XEZRN',9,'abcdefjhjkl'),('yes','g','abcdefjhjkl',10,'been'),('e','u','p',11,'v'),('FFJIH','j','ARQHA',8,'abcdefjhjkl'),('BNEQB','FCIPV','e',13,'XSECZ'),('JMRST','NDHZT','q',15,'oh'),('abcdefjhjkl','then','oh',16,'abcdefjhjkl'),('k','don\'t','abcdefjhjkl',17,'go'),('v','abcdefjhjkl','PMPKB',18,'about'),('abcdefjhjkl','AIKFD','abcdefjhjkl',19,'she'),('abcdefjhjkl','yeah','oh',20,'b'),('z','just','TSDNQ',21,'abcdefjhjkl'),('MVDMO','abcdefjhjkl','some',22,'abcdefjhjkl'),('abcdefjhjkl','UPOQH','h',23,'had');
SELECT table2.col_varchar_10_latin1_key FROM t1 AS table1 JOIN t2 AS table2 ON table1.col_varchar_1024_latin1_key = table2.col_varchar_10_latin1 WHERE table2.col_varchar_10_utf8 OR table1.pk ;
Test case:
SET SESSION optimizer_use_mrr = 'force';
SET SESSION join_cache_level = 6;
SET SESSION join_buffer_size = 1024;
--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_latin1 varchar(10) DEFAULT NULL, 1024_latin1_ key varchar(1024) DEFAULT NULL, 10_utf8 varchar(10) CHARACTER SET utf8 DEFAULT NULL, 10_latin1_ key (col_varchar_ 10_latin1_ key), 1024_latin1_ key (col_varchar_ 1024_latin1_ key) ,1,'mean' ,'z','CETCI' ),('r', 2,'abcdefjhjkl' ,'abcdefjhjkl' ,'g'),( 'been', 3,'abcdefjhjkl' ,'in',' hey'),( 'I\'ll' ,4,'abcdefjhjkl ','abcdefjhjkl' ,'CVCZR' ),('GSMTS' ,6,'he\ 's','abcdefjhjk l','u') ,('GSKUD' ,7,'h', 'at','the' ),('going' ,8,'this' ,'RNEVG' ,'JVXGC' ),('say' ,9,'WGBVU' ,'ZNQVR' ,'SOFFU' ),('you\ 're',10, 'z','j' ,'tell' ),('abcdefjhjkl ',14,'abcdefjhj kl','no' ,'p'),( 'NOFJH' ,15,'GLSXZ' ,'k','DWTZJ' ),('b', 16,'could' ,'JFJAU' ,'LIPLE' ),('abcdefjhjkl ',17,'z' ,'f','IGFKY' ),('abcdefjhjkl ',18,'abcdefjhj kl','g' ,'e'),( 'didn\' t',5,'this' ,'this' ,'would' ),('AUWVN' ,12,'abcdefjhjk l','AOOUK' ,'EHGSK' ),('QNHWK' ,20,'q' ,'on',' m'),('x' ,22,'n' ,'it',' abcdefjhjkl' ),('mean' ,23,'KVJEK' ,'have' ,'right' ),('h', 24,'VJVDS' ,'abcdefjhjkl' ,'XJJVP' ),('if' ,26,'u' ,'abcdefjhjkl' ,'CLULE' ),('abcdefjhjkl ',28,'you\ 're','abcdefjhj kl','think' ),('with' ,30,'z' ,'on',' n'),('s' ,31,'s' ,'MUVHW' ,'a'),( 'abcdefjhjkl' ,33,'LCVTC' ,'she', 'abcdefjhjkl' ),('m', 19,'for' ,'GNCJI' ,'abcdefjhjkl' ),('abcdefjhjkl ',11,'up' ,'all', 'it\'s' ),('g', 21,'u', 'abcdefjhjkl' ,'then' ),('t', 25,'abcdefjhjkl ','MQFEC' ,'right' ),('out' ,34,'abcdefjhjk l','g', 'GRPDG' ),('your' ,35,'can\ 't','RXSIK' ,'b'),( 'b',37, 'abcdefjhjkl' ,'FZCZL' ,'d'),( 'to',42, 'm','will' ,'l'),( 'abcdefjhjkl' ,43,'abcdefjhjk l','TNOSX' ,'PWQKO' );
col_varchar_
pk int(11) NOT NULL AUTO_INCREMENT,
col_varchar_
col_varchar_
col_varchar_
PRIMARY KEY (pk),
KEY col_varchar_
KEY col_varchar_
) ENGINE=Aria AUTO_INCREMENT=51 DEFAULT CHARSET=latin1 PAGE_CHECKSUM=1;
INSERT INTO t1 VALUES ('FSLXI'
CREATE TABLE t2 ( 10_utf8 varchar(10) CHARACTER SET utf8 DEFAULT NULL, 10_latin1 varchar(10) DEFAULT NULL, 10_latin1_ key varchar(10) DEFAULT NULL, 1024_latin1_ key varchar(1024) DEFAULT NULL, 10_latin1_ key (col_varchar_ 10_latin1_ key), 1024_latin1_ key (col_varchar_ 1024_latin1_ key) ,'abcdefjhjkl' ,'c',1, 'i'),(' m','abcdefjhjkl ','ILVHS' ,2,'abcdefjhjkl '),('he\ 's','abcdefjhjk l','were' ,3,'PRNZK' ),('d', 'abcdefjhjkl' ,'here' ,4,'abcdefjhjkl '),('can' ,'BLJCV' ,'go',5, 'that') ,('this' ,'SWGQI' ,'BFBWJ' ,6,'QECYF' ),('time' ,'p','m' ,7,'q') ,('abcdefjhjkl' ,'is',' XEZRN', 9,'abcdefjhjkl' ),('yes' ,'g','abcdefjhj kl',10, 'been') ,('e',' u','p', 11,'v') ,('FFJIH' ,'j','ARQHA' ,8,'abcdefjhjkl '),('BNEQB' ,'FCIPV' ,'e',13, 'XSECZ' ),('JMRST' ,'NDHZT' ,'q',15, 'oh'),( 'abcdefjhjkl' ,'then' ,'oh',16, 'abcdefjhjkl' ),('k', 'don\'t' ,'abcdefjhjkl' ,17,'go' ),('v', 'abcdefjhjkl' ,'PMPKB' ,18,'about' ),('abcdefjhjkl ','AIKFD' ,'abcdefjhjkl' ,19,'she' ),('abcdefjhjkl ','yeah' ,'oh',20, 'b'),(' z','just' ,'TSDNQ' ,21,'abcdefjhjk l'),('MVDMO' ,'abcdefjhjkl' ,'some' ,22,'abcdefjhjk l'),('abcdefjhj kl','UPOQH' ,'h',23, 'had');
col_varchar_
col_varchar_
col_varchar_
pk int(11) NOT NULL AUTO_INCREMENT,
col_varchar_
PRIMARY KEY (pk),
KEY col_varchar_
KEY col_varchar_
) ENGINE=Aria AUTO_INCREMENT=51 DEFAULT CHARSET=latin1 PAGE_CHECKSUM=1;
INSERT INTO t2 VALUES ('GINHU'
SELECT table2. col_varchar_ 10_latin1_ key col_varchar_ 1024_latin1_ key = table2. col_varchar_ 10_latin1 col_varchar_ 10_utf8 OR table1.pk ;
FROM t1 AS table1 JOIN t2 AS table2 ON table1.
WHERE table2.