MariaDB [test]> set optimizer_switch = 'optimize_join_buffer_size=on';
|
Query OK, 0 rows affected (0.000 sec)
|
MariaDB [test]> SELECT * FROM t1 LEFT JOIN ( t2 LEFT JOIN t3 ON f2 = f3 INNER JOIN t4 ) ON f1 = f2;
|
+------+------+------+------+
|
| f1 | f2 | f3 | f4 |
|
+------+------+------+------+
|
| 4 | NULL | NULL | NULL |
|
| 5 | NULL | NULL | NULL |
|
| 8 | NULL | NULL | NULL |
|
| 4 | NULL | NULL | NULL |
|
| 1 | NULL | NULL | NULL |
|
| 4 | NULL | NULL | NULL |
|
| 8 | NULL | NULL | NULL |
|
| 6 | NULL | NULL | NULL |
|
| 4 | NULL | NULL | NULL |
|
| 9 | NULL | NULL | NULL |
|
| 2 | NULL | NULL | NULL |
|
| 10 | NULL | NULL | NULL |
|
| 9 | NULL | NULL | NULL |
|
+------+------+------+------+
|
13 rows in set (0.001 sec)
|
|
MariaDB [test]> SELECT f3 FROM t1 LEFT JOIN ( t2 LEFT JOIN t3 ON f2 = f3 INNER JOIN t4 ) ON f1 = f2;
|
+------+
|
| f3 |
|
+------+
|
| NULL |
|
| NULL |
|
| NULL |
|
| NULL |
|
| NULL |
|
| NULL |
|
| NULL |
|
| NULL |
|
| NULL |
|
| NULL |
|
| NULL |
|
| 10 |
|
| NULL |
|
+------+
|
13 rows in set (0.001 sec)
|
|
MariaDB [test]> set optimizer_switch = 'optimize_join_buffer_size=off';
|
Query OK, 0 rows affected (0.000 sec)
|
|
MariaDB [test]> analyze SELECT f3 FROM t1 LEFT JOIN ( t2 LEFT JOIN t3 ON f2 = f3 INNER JOIN t4 ) ON f1 = f2;
|
+------+-------------+-------+------+---------------+------+---------+------+------+--------+----------+------------+--------------------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra |
|
+------+-------------+-------+------+---------------+------+---------+------+------+--------+----------+------------+--------------------------------------------------------+
|
| 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 13 | 13.00 | 100.00 | 100.00 | |
|
| 1 | SIMPLE | t4 | ALL | NULL | NULL | NULL | NULL | 0 | 0.00 | 0.00 | 100.00 | Using join buffer (flat, BNL join) |
|
| 1 | SIMPLE | t2 | ALL | NULL | NULL | NULL | NULL | 2 | NULL | 100.00 | NULL | Using where; Using join buffer (incremental, BNL join) |
|
| 1 | SIMPLE | t3 | ALL | NULL | NULL | NULL | NULL | 1 | NULL | 100.00 | NULL | Using where; Using join buffer (incremental, BNL join) |
|
+------+-------------+-------+------+---------------+------+---------+------+------+--------+----------+------------+--------------------------------------------------------+
|
4 rows in set (0.000 sec)
|
|
MariaDB [test]> set optimizer_switch = 'optimize_join_buffer_size=on';
|
Query OK, 0 rows affected (0.000 sec)
|
|
MariaDB [test]> analyze SELECT f3 FROM t1 LEFT JOIN ( t2 LEFT JOIN t3 ON f2 = f3 INNER JOIN t4 ) ON f1 = f2;
|
+------+-------------+-------+------+---------------+------+---------+------+------+--------+----------+------------+--------------------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra |
|
+------+-------------+-------+------+---------------+------+---------+------+------+--------+----------+------------+--------------------------------------------------------+
|
| 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 13 | 13.00 | 100.00 | 100.00 | |
|
| 1 | SIMPLE | t4 | ALL | NULL | NULL | NULL | NULL | 0 | 0.00 | 0.00 | 100.00 | Using join buffer (flat, BNL join) |
|
| 1 | SIMPLE | t2 | ALL | NULL | NULL | NULL | NULL | 2 | NULL | 100.00 | NULL | Using where; Using join buffer (incremental, BNL join) |
|
| 1 | SIMPLE | t3 | ALL | NULL | NULL | NULL | NULL | 1 | 1.00 | 100.00 | 650.00 | Using where; Using join buffer (incremental, BNL join) |
|
+------+-------------+-------+------+---------------+------+---------+------+------+--------+----------+------------+--------------------------------------------------------+
|
4 rows in set (0.001 sec)
|