Description:
mysql> set join_buffer_size=10737418240;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT a.char_col col1, a.char_col col2, ifnull(a.char_col,a.json_col) as col3, COUNT(a.bool_col) col4 FROM multi_tbl_3_all_typeindex a left join tbl_1_all_typeindex b on (a.char_col,a.json_col) in (select char_col col1, json_col col2 from view_tbl_3_all_type2index_desc a left join (SELECT char_col col1, json_col col2 from tbl_1_all_typeindex where tbl_1_all_typeindex.char_col <>
(select char_col from tbl_1_all_typeindex order by 1 limit 1)) b on (a.char_col,a.json_col) not in (select char_col,json_col from tbl_1_all_typeindex order by 1,2) order by 1,2) group by 1,2,3 order by 1,2,3,4;
+------+------+------+---------+
| col1 | col2 | col3 | col4 |
+------+------+------+---------+
| NULL | NULL | NULL | 50 |
| aaa2 | aaa2 | aaa2 | 11300 |
| aaaa | aaaa | aaaa | 25425 |
| aabb | aabb | aabb | 1494425 |
| aacd | aacd | aacd | 11300 |
| ab | ab | ab | 55596 |
+------+------+------+---------+
6 rows in set (9.50 sec)
mysql> set join_buffer_size=262144;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT a.char_col col1, a.char_col col2, ifnull(a.char_col,a.json_col) as col3, COUNT(a.bool_col) col4 FROM multi_tbl_3_all_typeindex a left join tbl_1_all_typeindex b on (a.char_col,a.json_col) in (select char_col col1, json_col col2 from view_tbl_3_all_type2index_desc a left join (SELECT char_col col1, json_col col2 from tbl_1_all_typeindex where tbl_1_all_typeindex.char_col <>
(select char_col from tbl_1_all_typeindex order by 1 limit 1)) b on (a.char_col,a.json_col) not in (select char_col,json_col from tbl_1_all_typeindex order by 1,2) order by 1,2) group by 1,2,3 order by 1,2,3,4;
+------+------+------+-------+
| col1 | col2 | col3 | col4 |
+------+------+------+-------+
| NULL | NULL | NULL | 50 |
| aaa2 | aaa2 | aaa2 | 1130 |
| aaaa | aaaa | aaaa | 1695 |
| aabb | aabb | aabb | 12995 |
| aacd | aacd | aacd | 1130 |
| ab | ab | ab | 2938 |
+------+------+------+-------+
6 rows in set (0.03 sec)
mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.41 |
+-----------+
1 row in set (0.00 sec)
How to repeat:
create database test;
use test;
source multi_tbl_3_all_typeindex.sql
source tbl_1_all_typeindex.sql
source multi_tbl_3_all_type2index_desc.sql
create view view_tbl_3_all_type2index_desc as select * from multi_tbl_3_all_type2index_desc;
set join_buffer_size=10737418240;
SELECT a.char_col col1, a.char_col col2, ifnull(a.char_col,a.json_col) as col3, COUNT(a.bool_col) col4 FROM multi_tbl_3_all_typeindex a left join tbl_1_all_typeindex b on (a.char_col,a.json_col) in (select char_col col1, json_col col2 from view_tbl_3_all_type2index_desc a left join (SELECT char_col col1, json_col col2 from tbl_1_all_typeindex where tbl_1_all_typeindex.char_col <>(select char_col from tbl_1_all_typeindex order by 1 limit 1)) b on (a.char_col,a.json_col) not in (select char_col,json_col from tbl_1_all_typeindex order by 1,2) order by 1,2) group by 1,2,3 order by 1,2,3,4;
set join_buffer_size=262144;
SELECT a.char_col col1, a.char_col col2, ifnull(a.char_col,a.json_col) as col3, COUNT(a.bool_col) col4 FROM multi_tbl_3_all_typeindex a left join tbl_1_all_typeindex b on (a.char_col,a.json_col) in (select char_col col1, json_col col2 from view_tbl_3_all_type2index_desc a left join (SELECT char_col col1, json_col col2 from tbl_1_all_typeindex where tbl_1_all_typeindex.char_col <>(select char_col from tbl_1_all_typeindex order by 1 limit 1)) b on (a.char_col,a.json_col) not in (select char_col,json_col from tbl_1_all_typeindex order by 1,2) order by 1,2) group by 1,2,3 order by 1,2,3,4;
