|
Explain for the query with join_cache_level=4
explain
|
SELECT SQL_NO_CACHE
|
(SELECT SUM(c.a) FROM t1 ttt, t2 ccc
|
WHERE ttt.a = ccc.b AND ttt.a = t.a GROUP BY ttt.a) AS minid
|
FROM t1 t, t2 c WHERE t.a = c.b;
|
id select_type table type possible_keys key key_len ref rows Extra
|
1 PRIMARY c ALL NULL NULL NULL NULL 2 Using where
|
1 PRIMARY t hash_index PRIMARY #hash#PRIMARY:PRIMARY 4:4 test.c.b 2 Using index; Using join buffer (flat, BNLH join)
|
2 DEPENDENT SUBQUERY ttt eq_ref PRIMARY PRIMARY 4 test.t.a 1 Using index
|
2 DEPENDENT SUBQUERY ccc hash_ALL NULL #hash#$hj 5 test.t.a 2 Using where; Using join buffer (flat, BNLH join)
|
|
|
Out of explain format=json
explain format=json
|
SELECT SQL_NO_CACHE
|
(SELECT SUM(c.a) FROM t1 ttt, t2 ccc
|
WHERE ttt.a = ccc.b AND ttt.a = t.a GROUP BY ttt.a) AS minid
|
FROM t1 t, t2 c WHERE t.a = c.b;
|
EXPLAIN
|
{
|
"query_block": {
|
"select_id": 1,
|
"table": {
|
"table_name": "c",
|
"access_type": "ALL",
|
"rows": 2,
|
"filtered": 100,
|
"attached_condition": "c.b is not null"
|
},
|
"block-nl-join": {
|
"table": {
|
"table_name": "t",
|
"access_type": "hash_index",
|
"possible_keys": ["PRIMARY"],
|
"key": "#hash#PRIMARY:PRIMARY",
|
"key_length": "4:4",
|
"used_key_parts": ["a"],
|
"ref": ["test.c.b"],
|
"rows": 2,
|
"filtered": 50,
|
"using_index": true
|
},
|
"buffer_type": "flat",
|
"buffer_size": "256Kb",
|
"join_type": "BNLH"
|
},
|
"subqueries": [
|
{
|
"query_block": {
|
"select_id": 2,
|
"table": {
|
"table_name": "ttt",
|
"access_type": "eq_ref",
|
"possible_keys": ["PRIMARY"],
|
"key": "PRIMARY",
|
"key_length": "4",
|
"used_key_parts": ["a"],
|
"ref": ["test.t.a"],
|
"rows": 1,
|
"filtered": 100,
|
"using_index": true
|
},
|
"block-nl-join": {
|
"table": {
|
"table_name": "ccc",
|
"access_type": "hash_ALL",
|
"key": "#hash#$hj",
|
"key_length": "5",
|
"used_key_parts": ["b"],
|
"ref": ["test.t.a"],
|
"rows": 2,
|
"filtered": 100
|
},
|
"buffer_type": "flat",
|
"buffer_size": "256Kb",
|
"join_type": "BNLH",
|
"attached_condition": "ccc.b = ttt.a"
|
}
|
}
|
}
|
]
|
}
|
}
|
|
|
|
Simplified test-case:
SET optimizer_switch='subquery_cache=off';
|
CREATE TABLE t1 (a INT , b INT);
|
INSERT INTO t1 VALUES (1,1) ,(2,2);
|
CREATE TABLE t2 (a INT , b INT);
|
INSERT INTO t2 VALUES (1,1), (3,3);
|
set join_cache_level=2;
|
SELECT
|
(SELECT sum(c.a) FROM t1 ttt, t2 ccc
|
WHERE ttt.a = ccc.b AND ttt.a = t.a ) AS minid
|
FROM t1 t, t2 c WHERE t.a = c.b;
|
|