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 ;