## 
## Use 'irrational' joins to compare columns longer than the key length of the index.
##
USE performance_schema;

## HA_KEY_TYPE_TEXT
## users.user char(32) vs. hosts.host char(60)

EXPLAIN SELECT * FROM (users AS t1 INNER JOIN hosts AS t2 ON (t2.host = t1.user));
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t2	NULL	ALL	NULL	NULL	NULL	NULL	#	100.00	NULL
1	SIMPLE	t1	NULL	ref	USER	USER	129	func	#	100.00	Using where
Warnings:
Note	1003	/* select#1 */ select `performance_schema`.`t1`.`USER` AS `USER`,`performance_schema`.`t1`.`CURRENT_CONNECTIONS` AS `CURRENT_CONNECTIONS`,`performance_schema`.`t1`.`TOTAL_CONNECTIONS` AS `TOTAL_CONNECTIONS`,`performance_schema`.`t2`.`HOST` AS `HOST`,`performance_schema`.`t2`.`CURRENT_CONNECTIONS` AS `CURRENT_CONNECTIONS`,`performance_schema`.`t2`.`TOTAL_CONNECTIONS` AS `TOTAL_CONNECTIONS` from `performance_schema`.`users` `t1` join `performance_schema`.`hosts` `t2` where (convert(`performance_schema`.`t2`.`HOST` using utf8mb4) = `performance_schema`.`t1`.`USER`)

SELECT COUNT(*) FROM (users AS t1 INNER JOIN hosts AS t2 ON (t2.host = t1.user));
COUNT(*)
#

## HA_KEY_TYPE_VARCHAR1 or HA_KEY_TYPE_VARCHAR2
## file_instances.file_name varchar(512) vs. session_variables.variable_name varchar(1024)

EXPLAIN SELECT * FROM (file_instances AS t1 INNER JOIN session_variables AS t2 ON (t2.variable_value = t1.file_name));
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t2	NULL	ALL	NULL	NULL	NULL	NULL	#	100.00	Using where
1	SIMPLE	t1	NULL	eq_ref	PRIMARY	PRIMARY	2050	performance_schema.t2.VARIABLE_VALUE	#	100.00	Using where
Warnings:
Note	1003	/* select#1 */ select `performance_schema`.`t1`.`FILE_NAME` AS `FILE_NAME`,`performance_schema`.`t1`.`EVENT_NAME` AS `EVENT_NAME`,`performance_schema`.`t1`.`OPEN_COUNT` AS `OPEN_COUNT`,`performance_schema`.`t2`.`VARIABLE_NAME` AS `VARIABLE_NAME`,`performance_schema`.`t2`.`VARIABLE_VALUE` AS `VARIABLE_VALUE` from `performance_schema`.`file_instances` `t1` join `performance_schema`.`session_variables` `t2` where (`performance_schema`.`t2`.`VARIABLE_VALUE` = `performance_schema`.`t1`.`FILE_NAME`)

SELECT COUNT(*) FROM (file_instances AS t1 INNER JOIN session_variables AS t2 ON (t2.variable_value = t1.file_name));
COUNT(*)
#