|
Let's use the settings:
set join_cache_level=4;
|
set join_buffer_space_limit;
|
and look at the output of EXPLAIN for the query:
MariaDB [test]> explain select count(*) from t1,t2,t3 left join t4 on t3.pk=t4.a where t1.a=t2.pk and t2.pk=t3.a;
|
+------+-------------+-------+------------+---------------+-----------+---------+------------+------+---------------------------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+------------+---------------+-----------+---------+------------+------+---------------------------------------------------------------+
|
| 1 | SIMPLE | t3 | ALL | NULL | NULL | NULL | NULL | 5 | Using where |
|
| 1 | SIMPLE | t4 | hash_index | a | #hash#a:a | 515:515 | test.t3.pk | 5 | Using where; Using index; Using join buffer (flat, BNLH join) |
|
| 1 | SIMPLE | t1 | hash_ALL | NULL | #hash#$hj | 515 | test.t3.a | 10 | Using where |
|
| 1 | SIMPLE | t2 | hash_ALL | NULL | #hash#$hj | 515 | test.t3.a | 10 | Using where |
|
MariaDB [test]> explain select count(*) from t1,t2,t3 left join t4 on t3.pk=t4.a where t1.a=t2.pk and t2.pk=t3.a;
|
+------+-------------+-------+------------+---------------+-----------+---------+------------+------+---------------------------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+------------+---------------+-----------+---------+------------+------+---------------------------------------------------------------+
|
| 1 | SIMPLE | t3 | ALL | NULL | NULL | NULL | NULL | 5 | Using where |
|
| 1 | SIMPLE | t4 | hash_index | a | #hash#a:a | 515:515 | test.t3.pk | 5 | Using where; Using index; Using join buffer (flat, BNLH join) |
|
| 1 | SIMPLE | t1 | hash_ALL | NULL | #hash#$hj | 515 | test.t3.a | 10 | Using where |
|
| 1 | SIMPLE | t2 | hash_ALL | NULL | #hash#$hj | 515 | test.t3.a | 10 | Using where |
|
+------+-------------+-------+------------+---------------+-----------+---------+------------+------+---------------------------------------------------------------+
|
+------+-------------+-------+------------+---------------+-----------+---------+------------+------+---------------------------------------------------------------+
|
A strange thing that we can observe here is that t1 and t2 are joined with hash join but no usage of join buffer is shown for these joins.
|
|
The output from EXPLAIN FPRMAT=JSON shows why the query returns a wrong result:
MariaDB [test]> explain format=json select count(*) from t1,t2,t3 left join t4 on t3.pk=t4.a where t1.a=t2.pk and t2.pk=t3.a;
|
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| EXPLAIN |
|
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| {
|
"query_block": {
|
"select_id": 1,
|
"table": {
|
"table_name": "t3",
|
"access_type": "ALL",
|
"rows": 5,
|
"filtered": 100,
|
"attached_condition": "t3.a is not null and t3.a is not null"
|
},
|
"block-nl-join": {
|
"table": {
|
"table_name": "t4",
|
"access_type": "hash_index",
|
"possible_keys": ["a"],
|
"key": "#hash#a:a",
|
"key_length": "515:515",
|
"used_key_parts": ["a"],
|
"ref": ["test.t3.pk"],
|
"rows": 5,
|
"filtered": 20,
|
"using_index": true
|
},
|
"buffer_type": "flat",
|
"buffer_size": "4Kb",
|
"join_type": "BNLH",
|
"attached_condition": "trigcond(trigcond(t3.pk is not null))"
|
},
|
"table": {
|
"table_name": "t1",
|
"access_type": "hash_ALL",
|
"key": "#hash#$hj",
|
"key_length": "515",
|
"used_key_parts": ["a"],
|
"ref": ["test.t3.a"],
|
"rows": 10,
|
"filtered": 100,
|
"attached_condition": "t1.a = t3.a"
|
},
|
"table": {
|
"table_name": "t2",
|
"access_type": "hash_ALL",
|
"key": "#hash#$hj",
|
"key_length": "515",
|
"used_key_parts": ["pk"],
|
"ref": ["test.t3.a"],
|
"rows": 10,
|
"filtered": 100,
|
"attached_condition": "t2.pk = t3.a"
|
}
|
}
|
} |
|
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
We see that condition t3.pk=t4.a was lost.
|