-- 5.7.44 rm -rf 114512/ bin/mysqld --no-defaults --initialize-insecure --basedir=$PWD --datadir=$PWD/114512 --log-error-verbosity=3 bin/mysqld_safe --no-defaults --basedir=$PWD --datadir=$PWD/114512 --core-file --socket=/tmp/mysql.sock --port=3306 --log-error=$PWD/114512/log.err --log-error-verbosity=3 --secure-file-priv="" --local-infile=1 2>&1 & bin/mysql -uroot -S /tmp/mysql.sock Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.7.44 MySQL Community Server (GPL) Copyright (c) 2000, 2023, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> create database test; Query OK, 1 row affected (0.00 sec) mysql> use test Database changed mysql> drop table if exists test_join1; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> drop table if exists test_join2; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> drop table if exists test_client cascade; Query OK, 0 rows affected, 1 warning (0.00 sec) . mysql> create table if not exists test_client ( id int auto_increment primary key, col1 varchar(255), col2 varchar(255), col3 varchar(255), col4 varchar(255), col5 varchar(255), col6 varchar(255), col7 varchar(255), col8 varchar(255), col9 varchar(255), col10 varchar(255), col11 varchar(255), col12 varchar(255), col13 varchar(255), col14 varchar(255), col15 varchar(255), col16 varchar(255), col17 varchar(255), col18 varchar(255), col19 varchar(255), col20 varchar(255), col21 varchar(255), col22 varchar(255), col23 varchar(255), col24 varchar(255), col25 varchar(255), col26 varchar(255), col27 varchar(255), col28 varchar(255), col29 varchar(255), col30 varchar(255), col31 varchar(255), col32 varchar(255), col33 varchar(255), col34 varchar(255), col35 varchar(255), col36 varchar(255), col37 varchar(255), col38 varchar(255), col39 varchar(255), col40 varchar(255), col41 varchar(255), col42 varchar(255), col43 varchar(255), col44 varchar(255), col45 varchar(255), col46 varchar(255), col47 varchar(255), col48 varchar(255), col49 varchar(255) ); -> id int auto_increment primary key, -> col1 varchar(255), -> col2 varchar(255), -> col3 varchar(255), -> col4 varchar(255), -> col5 varchar(255), -> col6 varchar(255), -> col7 varchar(255), -> col8 varchar(255), -> col9 varchar(255), -> col10 varchar(255), -> col11 varchar(255), -> col12 varchar(255), -> col13 varchar(255), -> col14 varchar(255), -> col15 varchar(255), -> col16 varchar(255), -> col17 varchar(255), -> col18 varchar(255), -> col19 varchar(255), -> col20 varchar(255), -> col21 varchar(255), -> col22 varchar(255), -> col23 varchar(255), -> col24 varchar(255), -> col25 varchar(255), -> col26 varchar(255), -> col27 varchar(255), -> col28 varchar(255), -> col29 varchar(255), -> col30 varchar(255), -> col31 varchar(255), -> col32 varchar(255), -> col33 varchar(255), -> col34 varchar(255), -> col35 varchar(255), -> col36 varchar(255), -> col37 varchar(255), -> col38 varchar(255), -> col39 varchar(255), -> col40 varchar(255), -> col41 varchar(255), -> col42 varchar(255), -> col43 varchar(255), -> col44 varchar(255), -> col45 varchar(255), -> col46 varchar(255), -> col47 varchar(255), -> col48 varchar(255), -> col49 varchar(255) -> ); Query OK, 0 rows affected (0.01 sec) mysql> create table if not exists test_join1 ( -> id int auto_increment primary key, -> client_id int, -> col1 varchar(255), -> col2 varchar(255), -> col3 varchar(255), -> col4 varchar(255), -> col5 varchar(255), col16 varchar(255), col17 varchar(255), col18 varchar(255), col19 varchar(255), col20 varchar(255), col21 varchar(255), col22 varchar(255), col23 varchar(255), col24 varchar(255), col25 varchar(255), col26 varchar(255), col27 varchar(255), col28 varchar(255), col29 varchar(255), col30 varchar(255), col31 varchar(255), col32 varchar(255), -> col6 varchar(255), -> col7 varchar(255), -> col8 varchar(255), -> col9 varchar(255), -> col10 varchar(255), -> col11 varchar(255), -> col12 varchar(255), -> col13 varchar(255), -> col14 varchar(255), -> col15 varchar(255), -> col16 varchar(255), -> col17 varchar(255), -> col18 varchar(255), -> col19 varchar(255), -> col20 varchar(255), -> col21 varchar(255), -> col22 varchar(255), -> col23 varchar(255), -> col24 varchar(255), -> col25 varchar(255), -> col26 varchar(255), -> col27 varchar(255), -> col28 varchar(255), -> col29 varchar(255), -> col30 varchar(255), -> col31 varchar(255), -> col32 varchar(255), -> col33 varchar(255), -> col34 varchar(255), -> col35 varchar(255), -> col36 varchar(255), -> col37 varchar(255), -> col38 varchar(255), -> col39 varchar(255), -> col40 varchar(255), -> col41 varchar(255), -> col42 varchar(255), -> col43 varchar(255), -> col44 varchar(255), -> col45 varchar(255), -> col46 varchar(255), -> col47 varchar(255), -> col48 varchar(255), -> col49 varchar(255), -> foreign key (client_id) references test_client(id) -> ); Query OK, 0 rows affected (0.01 sec) mysql> create table if not exists test_join2 ( -> id int auto_increment primary key, -> client_id int, -> col1 varchar(255), -> col2 varchar(255), -> col3 varchar(255), -> col4 varchar(255), -> col5 varchar(255), -> col6 varchar(255), -> col7 varchar(255), col33 varchar(255), col34 varchar(255), col35 varchar(255), col36 varchar(255), col37 varchar(255), col38 varchar(255), col39 varchar(255), col40 varchar(255), col41 varchar(255), col42 varchar(255), col43 varchar(255), col44 varchar(255), col45 varchar(255), col46 varchar(255), col47 varchar(255), col48 varchar(255), col49 varchar(255), -> col8 varchar(255), -> col9 varchar(255), -> col10 varchar(255), -> col11 varchar(255), -> col12 varchar(255), -> col13 varchar(255), -> col14 varchar(255), -> col15 varchar(255), -> col16 varchar(255), -> col17 varchar(255), -> col18 varchar(255), -> col19 varchar(255), -> col20 varchar(255), -> col21 varchar(255), -> col22 varchar(255), -> col23 varchar(255), -> col24 varchar(255), -> col25 varchar(255), -> col26 varchar(255), -> col27 varchar(255), -> col28 varchar(255), -> col29 varchar(255), -> col30 varchar(255), -> col31 varchar(255), -> col32 varchar(255), -> col33 varchar(255), -> col34 varchar(255), -> col35 varchar(255), -> col36 varchar(255), -> col37 varchar(255), -> col38 varchar(255), -> col39 varchar(255), -> col40 varchar(255), -> col41 varchar(255), -> col42 varchar(255), -> col43 varchar(255), -> col44 varchar(255), -> col45 varchar(255), -> col46 varchar(255), -> col47 varchar(255), -> col48 varchar(255), -> col49 varchar(255), -> foreign key (client_id) references test_client(id) -> ); Query OK, 0 rows affected (0.01 sec) -- -- exported test_client as CSV from 8.0.36 instance and imported in 5.7.44 mysql> LOAD DATA INFILE '/export/home/tmp/ushastry/mysql-5.7.44/test_client.txt' INTO TABLE test_client FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n'; Query OK, 1000001 rows affected (6.84 sec) Records: 1000001 Deleted: 0 Skipped: 0 Warnings: 0 mysql> optimize table test_client; +------------------+----------+----------+-------------------------------------------------------------------+ | Table | Op | Msg_type | Msg_text | +------------------+----------+----------+-------------------------------------------------------------------+ | test.test_client | optimize | note | Table does not support optimize, doing recreate + analyze instead | | test.test_client | optimize | status | OK | +------------------+----------+----------+-------------------------------------------------------------------+ 2 rows in set (7.50 sec) mysql> create index test_client_id on test_client (id); Query OK, 0 rows affected (2.16 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> optimize table test_client; +------------------+----------+----------+-------------------------------------------------------------------+ | Table | Op | Msg_type | Msg_text | +------------------+----------+----------+-------------------------------------------------------------------+ | test.test_client | optimize | note | Table does not support optimize, doing recreate + analyze instead | | test.test_client | optimize | status | OK | +------------------+----------+----------+-------------------------------------------------------------------+ 2 rows in set (8.53 sec) mysql> explain select -> test_client.*, -> test_join1.*, -> test_join2.* -> from -> test_client -> left join test_join1 on test_client.id = test_join1.client_id -> left join test_join2 on test_client.id = test_join2.client_id -> order by test_client.id -> limit 10; +----+-------------+-------------+------------+------+---------------+------+---------+------+--------+----------+----------------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------------+------------+------+---------------+------+---------+------+--------+----------+----------------------------------------------------+ | 1 | SIMPLE | test_client | NULL | ALL | NULL | NULL | NULL | NULL | 994944 | 100.00 | Using temporary; Using filesort | | 1 | SIMPLE | test_join1 | NULL | ALL | client_id | NULL | NULL | NULL | 1 | 100.00 | Using where; Using join buffer (Block Nested Loop) | | 1 | SIMPLE | test_join2 | NULL | ALL | client_id | NULL | NULL | NULL | 1 | 100.00 | Using where; Using join buffer (Block Nested Loop) | +----+-------------+-------------+------------+------+---------------+------+---------+------+--------+----------+----------------------------------------------------+ 3 rows in set, 1 warning (0.00 sec) mysql> select -> test_client.*, -> test_join1.*, -> test_join2.* -> from -> test_client -> left join test_join1 on test_client.id = test_join1.client_id -> left join test_join2 on test_client.id = test_join2.client_id -> order by test_client.id -> limit 10; . 10 rows in set (8.10 sec) 10 rows in set (8.06 sec) 10 rows in set (8.05 sec) mysql> -- without ORDER BY mysql> mysql> explain select test_client.*, test_join1.*, test_join2.* from test_client left join test_join1 on test_client.id = test_join1.client_id left join test_join2 on test_client.id = test_join2.client_id limit -> 10; +----+-------------+-------------+------------+------+---------------+------+---------+------+--------+----------+----------------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------------+------------+------+---------------+------+---------+------+--------+----------+----------------------------------------------------+ | 1 | SIMPLE | test_client | NULL | ALL | NULL | NULL | NULL | NULL | 994944 | 100.00 | NULL | | 1 | SIMPLE | test_join1 | NULL | ALL | client_id | NULL | NULL | NULL | 1 | 100.00 | Using where; Using join buffer (Block Nested Loop) | | 1 | SIMPLE | test_join2 | NULL | ALL | client_id | NULL | NULL | NULL | 1 | 100.00 | Using where; Using join buffer (Block Nested Loop) | +----+-------------+-------------+------------+------+---------------+------+---------+------+--------+----------+----------------------------------------------------+ 3 rows in set, 1 warning (0.00 sec) mysql> select test_client.*, test_join1.*, test_join2.* from test_client left join test_join1 on test_client.id = test_join1.client_id left join test_join2 on test_client.id = test_join2.client_id limit 10; . 10 rows in set (0.01 sec) 10 rows in set (0.00 sec) 10 rows in set (0.01 sec) mysql> -- with NO_BNL mysql> mysql> explain select /*+ NO_BNL() */ test_client.*, test_join1.*, test_join2.* from test_client left join test_join1 on test_client.id = test_join1.client_id left join test_join2 on test_client.id = test_join2. -> client_id limit 10; +----+-------------+-------------+------------+------+---------------+-----------+---------+---------------------+--------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------------+------------+------+---------------+-----------+---------+---------------------+--------+----------+-------+ | 1 | SIMPLE | test_client | NULL | ALL | NULL | NULL | NULL | NULL | 994944 | 100.00 | NULL | | 1 | SIMPLE | test_join1 | NULL | ref | client_id | client_id | 5 | test.test_client.id | 1 | 100.00 | NULL | | 1 | SIMPLE | test_join2 | NULL | ref | client_id | client_id | 5 | test.test_client.id | 1 | 100.00 | NULL | +----+-------------+-------------+------------+------+---------------+-----------+---------+---------------------+--------+----------+-------+ 3 rows in set, 1 warning (0.00 sec) mysql> select /*+ NO_BNL() */ test_client.*, test_join1.*, test_join2.* from test_client left join test_join1 on test_client.id = test_join1.client_id left join test_join2 on test_client.id = test_join2. client_id limit 10; . 10 rows in set (0.00 sec) 10 rows in set (0.00 sec) 10 rows in set (0.00 sec) -- 8.0.36 rm -rf 114512/ bin/mysqld --no-defaults --initialize-insecure --basedir=$PWD --datadir=$PWD/114512 --log-error-verbosity=3 bin/mysqld_safe --no-defaults --mysqld-version='' --basedir=$PWD --datadir=$PWD/114512 --core-file --socket=/tmp/mysql.sock --port=3306 --log-error=$PWD/114512/log.err --mysqlx=0 --log-error-verbosity=3 --secure-file-priv="" --local-infile=1 2>&1 & bin/mysql -uroot -S /tmp/mysql.sock Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 7 Server version: 8.0.36 MySQL Community Server - GPL Copyright (c) 2000, 2024, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> create database test; Query OK, 1 row affected (0.00 sec) mysql> use test . . -> '', -> '' FROM nrows; Query OK, 1000001 rows affected (13.11 sec) Records: 1000001 Duplicates: 0 Warnings: 0 mysql> mysql> create index test_client_id on test_client (id); Query OK, 0 rows affected (2.85 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> optimize table test_client; +------------------+----------+----------+-------------------------------------------------------------------+ | Table | Op | Msg_type | Msg_text | +------------------+----------+----------+-------------------------------------------------------------------+ | test.test_client | optimize | note | Table does not support optimize, doing recreate + analyze instead | | test.test_client | optimize | status | OK | +------------------+----------+----------+-------------------------------------------------------------------+ 2 rows in set (11.73 sec) mysql> explain select -> test_client.*, -> test_join1.*, -> test_join2.* -> from -> test_client -> left join test_join1 on test_client.id = test_join1.client_id -> left join test_join2 on test_client.id = test_join2.client_id -> order by test_client.id -> limit 10; +----+-------------+-------------+------------+------+---------------+------+---------+------+--------+----------+--------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------------+------------+------+---------------+------+---------+------+--------+----------+--------------------------------------------+ | 1 | SIMPLE | test_client | NULL | ALL | NULL | NULL | NULL | NULL | 994944 | 100.00 | Using temporary; Using filesort | | 1 | SIMPLE | test_join1 | NULL | ALL | client_id | NULL | NULL | NULL | 1 | 100.00 | Using where; Using join buffer (hash join) | | 1 | SIMPLE | test_join2 | NULL | ALL | client_id | NULL | NULL | NULL | 1 | 100.00 | Using where; Using join buffer (hash join) | +----+-------------+-------------+------------+------+---------------+------+---------+------+--------+----------+--------------------------------------------+ 3 rows in set, 1 warning (0.01 sec) mysql> select -> test_client.*, -> test_join1.*, -> test_join2.* -> from -> test_client -> left join test_join1 on test_client.id = test_join1.client_id -> left join test_join2 on test_client.id = test_join2.client_id -> order by test_client.id -> limit 10; . 10 rows in set (7.66 sec) 10 rows in set (7.67 sec) 10 rows in set (7.67 sec) mysql> -- without ORDER BY mysql> explain select test_client.*, test_join1.*, test_join2.* from test_client left join test_join1 on test_client.id = test_join1.client_id left join test_join2 on test_client.id = test_join2.client_id limit 10; +----+-------------+-------------+------------+------+---------------+------+---------+------+--------+----------+--------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------------+------------+------+---------------+------+---------+------+--------+----------+--------------------------------------------+ | 1 | SIMPLE | test_client | NULL | ALL | NULL | NULL | NULL | NULL | 994944 | 100.00 | NULL | | 1 | SIMPLE | test_join1 | NULL | ALL | client_id | NULL | NULL | NULL | 1 | 100.00 | Using where; Using join buffer (hash join) | | 1 | SIMPLE | test_join2 | NULL | ALL | client_id | NULL | NULL | NULL | 1 | 100.00 | Using where; Using join buffer (hash join) | +----+-------------+-------------+------------+------+---------------+------+---------+------+--------+----------+--------------------------------------------+ 3 rows in set, 1 warning (0.00 sec) mysql> select test_client.*, test_join1.*, test_join2.* from test_client left join test_join1 on test_client.id = test_join1.client_id left join test_join2 on test_client.id = test_join2.client_id limit 10; . 10 rows in set (0.00 sec) 10 rows in set (0.00 sec) 10 rows in set (0.00 sec) mysql> -- with NO_BNL mysql> mysql> -- with NO_BNL mysql> mysql> explain select /*+ NO_BNL() */ test_client.*, test_join1.*, test_join2.* from test_client left join test_join1 on test_client.id = test_join1.client_id left join test_join2 on test_client.id = test_join2.client_id limit 10; +----+-------------+-------------+------------+------+---------------+-----------+---------+---------------------+--------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------------+------------+------+---------------+-----------+---------+---------------------+--------+----------+-------+ | 1 | SIMPLE | test_client | NULL | ALL | NULL | NULL | NULL | NULL | 994944 | 100.00 | NULL | | 1 | SIMPLE | test_join1 | NULL | ref | client_id | client_id | 5 | test.test_client.id | 1 | 100.00 | NULL | | 1 | SIMPLE | test_join2 | NULL | ref | client_id | client_id | 5 | test.test_client.id | 1 | 100.00 | NULL | +----+-------------+-------------+------------+------+---------------+-----------+---------+---------------------+--------+----------+-------+ 3 rows in set, 1 warning (0.00 sec) mysql> select /*+ NO_BNL() */ test_client.*, test_join1.*, test_join2.* from test_client left join test_join1 on test_client.id = test_join1.client_id left join test_join2 on test_client.id = test_join2.client_id limit 10; . 10 rows in set (0.00 sec) 10 rows in set (0.00 sec) 10 rows in set (0.00 sec) -- 8.3.0 rm -rf 114512/ bin/mysqld --no-defaults --initialize-insecure --basedir=$PWD --datadir=$PWD/114512 --log-error-verbosity=3 bin/mysqld_safe --no-defaults --mysqld-version='' --basedir=$PWD --datadir=$PWD/114512 --core-file --socket=/tmp/mysql.sock --port=3306 --log-error=$PWD/114512/log.err --mysqlx=0 --log-error-verbosity=3 --secure-file-priv="" --local-infile=1 2>&1 & bin/mysql -uroot -S /tmp/mysql.sock Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 7 Server version: 8.3.0 MySQL Community Server - GPL Copyright (c) 2000, 2024, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> create database test; Query OK, 1 row affected (0.00 sec) mysql> use test Database changed mysql> drop table if exists test_join1; Query OK, 0 rows affected, 1 warning (0.01 sec) mysql> drop table if exists test_join2; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> drop table if exists test_client cascade; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> mysql> create table if not exists test_client ( -> id int auto_increment primary key, -> col1 varchar(255), -> col2 varchar(255), -> col3 varchar(255), -> col4 varchar(255), -> col5 varchar(255), -> col6 varchar(255), -> col7 varchar(255), col17 varchar(255), col18 varchar(255), col19 varchar(255), col20 varchar(255), col21 varchar(255), col22 varchar(255), col23 varchar(255), col24 varchar(255), col25 varchar(255), col26 varchar(255) -> , col27 varchar(255), col28 varchar(255), col29 varchar(255), col30 varchar(255), col31 varchar(255), col32 varchar(255), col33 varchar(255), col8 varchar(255), -> col9 varchar(255), -> col10 varchar(255), -> col11 varchar(255), -> col12 varchar(255), -> col13 varchar(255), -> col14 varchar(255), -> col15 varchar(255), col34 varchar(255), col35 varchar(255), col36 varchar(255), col37 varchar(255), col38 varchar(255), col39 varchar(255), col40 varchar(255), col41 varchar(255), col42 varchar(255), col43 varchar(25 -> 5), col44 varchar(255), col45 varchar(255), col46 varchar(255), col47 varchar(255), col48 varchar(255), col49 varchar(255) ); col16 varchar(255), -> col17 varchar(255), -> col18 varchar(255), -> col19 varchar(255), -> col20 varchar(255), -> col21 varchar(255), -> col22 varchar(255), -> col23 varchar(255), -> col24 varchar(255), -> col25 varchar(255), -> col26 varchar(255), -> col27 varchar(255), -> col28 varchar(255), -> col29 varchar(255), -> col30 varchar(255), -> col31 varchar(255), -> col32 varchar(255), -> col33 varchar(255), -> col34 varchar(255), -> col35 varchar(255), -> col36 varchar(255), -> col37 varchar(255), -> col38 varchar(255), -> col39 varchar(255), -> col40 varchar(255), -> col41 varchar(255), -> col42 varchar(255), -> col43 varchar(255), -> col44 varchar(255), -> col45 varchar(255), -> col46 varchar(255), -> col47 varchar(255), -> col48 varchar(255), -> col49 varchar(255) -> ); Query OK, 0 rows affected (0.03 sec) mysql> create table if not exists test_join1 ( -> id int auto_increment primary key, -> client_id int, -> col1 varchar(255), -> col2 varchar(255), -> col3 varchar(255), -> col4 varchar(255), -> col5 varchar(255), -> col6 varchar(255), -> col7 varchar(255), -> col8 varchar(255), col16 varchar(255), col17 varchar(255), col18 varchar(255), col19 varchar(255), col20 varchar(255), col21 varchar(255), col22 varchar(255), col23 varchar(255), col24 varchar(255), col25 varchar(255) -> , col26 varchar(255), col27 varchar(255), col28 varchar(255), col29 varchar(255), col30 varchar(255), col31 varchar(255), col32 varchar(255), col9 varchar(255), -> col10 varchar(255), -> col11 varchar(255), -> col12 varchar(255), -> col13 varchar(255), -> col14 varchar(255), -> col15 varchar(255), -> col16 varchar(255), -> col17 varchar(255), -> col18 varchar(255), col33 varchar(255), col34 varchar(255), col35 varchar(255), col36 varchar(255), col37 varchar(255), col38 varchar(255), col39 varchar(255), col40 varchar(255), col41 varchar(255), col42 varchar(255), col43 varchar(255), col44 varchar(255), col45 varchar(255), col46 varchar(255), col47 varchar(255), col48 varchar(255), col49 varchar(255), -> col19 varchar(255), -> col20 varchar(255), -> col21 varchar(255), -> col22 varchar(255), -> col23 varchar(255), -> col24 varchar(255), -> col25 varchar(255), -> col26 varchar(255), -> col27 varchar(255), -> col28 varchar(255), -> col29 varchar(255), -> col30 varchar(255), -> col31 varchar(255), -> col32 varchar(255), -> col33 varchar(255), -> col34 varchar(255), -> col35 varchar(255), -> col36 varchar(255), -> col37 varchar(255), -> col38 varchar(255), -> col39 varchar(255), -> col40 varchar(255), -> col41 varchar(255), -> col42 varchar(255), -> col43 varchar(255), -> col44 varchar(255), -> col45 varchar(255), -> col46 varchar(255), -> col47 varchar(255), -> col48 varchar(255), -> col49 varchar(255), -> foreign key (client_id) references test_client(id) -> ); Query OK, 0 rows affected (0.04 sec) mysql> create table if not exists test_join2 ( -> id int auto_increment primary key, -> client_id int, -> col1 varchar(255), -> col2 varchar(255), -> col3 varchar(255), col16 varchar(255), col17 varchar(255), col18 varchar(255), col19 varchar(255), col20 varchar(255), col21 varchar(255), col22 varchar(255), col23 varchar(255), col24 varchar(255), col25 varchar(255), -> col26 varchar(255), col27 varchar(255), col28 varchar(255), col29 varchar(255), col30 varchar(255), col31 varchar(255), col32 varchar(255), col4 varchar(255), -> col5 varchar(255), -> col6 varchar(255), -> col7 varchar(255), -> col8 varchar(255), -> col9 varchar(255), col33 varchar(255), col34 varchar(255), col35 varchar(255), col36 varchar(255), col37 varchar(255), col38 varchar(255), col39 varchar(255), col40 varchar(255), col41 varchar(255), col42 varchar(255), col43 varchar(255), col44 varchar(255), col45 varchar(255), col46 varchar(255), col47 varchar(255), col48 varchar(255), col49 varchar(255), -> col10 varchar(255), -> col11 varchar(255), -> col12 varchar(255), -> col13 varchar(255), -> col14 varchar(255), -> col15 varchar(255), -> col16 varchar(255), -> col17 varchar(255), -> col18 varchar(255), -> col19 varchar(255), -> col20 varchar(255), -> col21 varchar(255), -> col22 varchar(255), -> col23 varchar(255), -> col24 varchar(255), -> col25 varchar(255), -> col26 varchar(255), -> col27 varchar(255), -> col28 varchar(255), -> col29 varchar(255), -> col30 varchar(255), -> col31 varchar(255), -> col32 varchar(255), -> col33 varchar(255), -> col34 varchar(255), -> col35 varchar(255), -> col36 varchar(255), -> col37 varchar(255), -> col38 varchar(255), -> col39 varchar(255), -> col40 varchar(255), -> col41 varchar(255), -> col42 varchar(255), -> col43 varchar(255), -> col44 varchar(255), -> col45 varchar(255), -> col46 varchar(255), -> col47 varchar(255), -> col48 varchar(255), -> col49 varchar(255), -> foreign key (client_id) references test_client(id) -> ); Query OK, 0 rows affected (0.03 sec) mysql> SET @@cte_max_recursion_depth = 1000001; insert into test_client WITH RECURSIVE nrows(id) AS ( SELECT 1 as id UNION ALL SELECT id + 1 from nrows WHERE id<=1000000 ) SELECT id, '', '', '', '', '', '', '', '', '', '', '', '', '', '', Query OK, 0 rows affected (0.00 sec) mysql> mysql> insert into test_client -> WITH RECURSIVE nrows(id) AS ( -> SELECT 1 as id UNION ALL -> SELECT id + 1 from nrows WHERE id<=1000000 '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', -> ) -> SELECT id, '', -> '', -> '', -> '', -> '', -> '', -> '', -> '', -> '', -> '', -> '', -> '', -> '', -> '', -> '', -> '', -> '', -> '', -> '', -> '', -> '', -> '', -> '', -> '', -> '', -> '', -> '', -> '', -> '', -> '', -> '', -> '', -> '', -> '', -> '', -> '', -> '', -> '', -> '', -> '', -> '', -> '', -> '', -> '', -> '', -> '', -> '', -> '', -> '' FROM nrows; Query OK, 1000001 rows affected (13.39 sec) Records: 1000001 Duplicates: 0 Warnings: 0 mysql> mysql> create index test_client_id on test_client (id); Query OK, 0 rows affected (2.82 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> optimize table test_client; +------------------+----------+----------+-------------------------------------------------------------------+ | Table | Op | Msg_type | Msg_text | +------------------+----------+----------+-------------------------------------------------------------------+ | test.test_client | optimize | note | Table does not support optimize, doing recreate + analyze instead | | test.test_client | optimize | status | OK | +------------------+----------+----------+-------------------------------------------------------------------+ 2 rows in set (12.01 sec) mysql> explain select -> test_client.*, -> test_join1.*, -> test_join2.* -> from -> test_client -> left join test_join1 on test_client.id = test_join1.client_id -> left join test_join2 on test_client.id = test_join2.client_id -> order by test_client.id -> limit 10; +----+-------------+-------------+------------+------+---------------+------+---------+------+--------+----------+--------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------------+------------+------+---------------+------+---------+------+--------+----------+--------------------------------------------+ | 1 | SIMPLE | test_client | NULL | ALL | NULL | NULL | NULL | NULL | 994944 | 100.00 | Using temporary; Using filesort | | 1 | SIMPLE | test_join1 | NULL | ALL | client_id | NULL | NULL | NULL | 1 | 100.00 | Using where; Using join buffer (hash join) | | 1 | SIMPLE | test_join2 | NULL | ALL | client_id | NULL | NULL | NULL | 1 | 100.00 | Using where; Using join buffer (hash join) | +----+-------------+-------------+------------+------+---------------+------+---------+------+--------+----------+--------------------------------------------+ 3 rows in set, 1 warning (0.01 sec) mysql> select -> test_client.*, -> test_join1.*, -> test_join2.* -> from -> test_client -> left join test_join1 on test_client.id = test_join1.client_id -> left join test_join2 on test_client.id = test_join2.client_id -> order by test_client.id -> limit 10; . 10 rows in set (7.79 sec) 10 rows in set (7.74 sec) 10 rows in set (7.74 sec) mysql> -- without ORDER BY Query OK, 0 rows affected (0.00 sec) mysql> mysql> explain select test_client.*, test_join1.*, test_join2.* from test_client left join test_join1 on test_client.id = test_join1.client_id left join test_join2 on test_client.id = test_join2.client_id limit 10; +----+-------------+-------------+------------+------+---------------+------+---------+------+--------+----------+--------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------------+------------+------+---------------+------+---------+------+--------+----------+--------------------------------------------+ | 1 | SIMPLE | test_client | NULL | ALL | NULL | NULL | NULL | NULL | 994944 | 100.00 | NULL | | 1 | SIMPLE | test_join1 | NULL | ALL | client_id | NULL | NULL | NULL | 1 | 100.00 | Using where; Using join buffer (hash join) | | 1 | SIMPLE | test_join2 | NULL | ALL | client_id | NULL | NULL | NULL | 1 | 100.00 | Using where; Using join buffer (hash join) | +----+-------------+-------------+------------+------+---------------+------+---------+------+--------+----------+--------------------------------------------+ 3 rows in set, 1 warning (0.00 sec) mysql> select test_client.*, test_join1.*, test_join2.* from test_client left join test_join1 on test_client.id = test_join1.client_id left join test_join2 on test_client.id = test_join2.client_id limit 10; . 10 rows in set (0.00 sec) 10 rows in set (0.00 sec) 10 rows in set (0.00 sec) mysql> -- with NO_BNL Query OK, 0 rows affected (0.00 sec) mysql> mysql> explain select /*+ NO_BNL() */ test_client.*, test_join1.*, test_join2.* from test_client left join test_join1 on test_client.id = test_join1.client_id left join test_join2 on test_client.id = test_join2.client_id limit 10; +----+-------------+-------------+------------+------+---------------+-----------+---------+---------------------+--------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------------+------------+------+---------------+-----------+---------+---------------------+--------+----------+-------+ | 1 | SIMPLE | test_client | NULL | ALL | NULL | NULL | NULL | NULL | 994944 | 100.00 | NULL | | 1 | SIMPLE | test_join1 | NULL | ref | client_id | client_id | 5 | test.test_client.id | 1 | 100.00 | NULL | | 1 | SIMPLE | test_join2 | NULL | ref | client_id | client_id | 5 | test.test_client.id | 1 | 100.00 | NULL | +----+-------------+-------------+------------+------+---------------+-----------+---------+---------------------+--------+----------+-------+ 3 rows in set, 1 warning (0.00 sec) mysql> select /*+ NO_BNL() */ test_client.*, test_join1.*, test_join2.* from test_client left join test_join1 on test_client.id = test_join1.client_id left join test_join2 on test_client.id = test_join2.client_id limit 10; . 10 rows in set (0.00 sec) 10 rows in set (0.00 sec) 10 rows in set (0.00 sec)