33 lines
2.3 KiB
Plaintext
33 lines
2.3 KiB
Plaintext
##
|
|
## 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(*)
|
|
#
|