Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.3(EOL)
-
None
Description
Let's take a small example (from main/derived_split_innodb.test) and look how LATERAL DERIVED is shown in EXPLAIN:
CREATE TABLE t1 ( |
n1 int(10) NOT NULL, |
n2 int(10) NOT NULL, |
c1 char(1) NOT NULL, |
KEY c1 (c1), |
KEY n1_c1_n2 (n1,c1,n2) |
) ENGINE=InnoDB;
|
INSERT INTO t1 VALUES (0, 2, 'a'), (1, 3, 'a'); |
ANALYZE TABLE t1; |
EXPLAIN SELECT t1.n1 FROM t1, (SELECT n1, n2 FROM t1 WHERE c1 = 'a' GROUP BY n1) as t
|
WHERE t.n1 = t1.n1 AND t.n2 = t1.n2 AND c1 = 'a' GROUP BY n1;
|
+------+-----------------+------------+-------+---------------+----------+---------+-------------------+------+--------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-----------------+------------+-------+---------------+----------+---------+-------------------+------+--------------------------+
|
| 1 | PRIMARY | t1 | index | c1,n1_c1_n2 | n1_c1_n2 | 9 | NULL | 2 | Using where; Using index |
|
| 1 | PRIMARY | <derived2> | ref | key0 | key0 | 8 | j5.t1.n1,j5.t1.n2 | 2 | |
|
| 2 | LATERAL DERIVED | t1 | ref | c1,n1_c1_n2 | n1_c1_n2 | 4 | j5.t1.n1 | 1 | Using where; Using index |
|
+------+-----------------+------------+-------+---------------+----------+---------+-------------------+------+--------------------------+
|
Good so far
Now, try EXPLAIN FORMAT=JSON
{
|
"query_block": {
|
"select_id": 1,
|
"table": {
|
"table_name": "t1",
|
"access_type": "index",
|
"possible_keys": ["c1", "n1_c1_n2"],
|
"key": "n1_c1_n2",
|
"key_length": "9",
|
"used_key_parts": ["n1", "c1", "n2"],
|
"rows": 2,
|
"filtered": 100,
|
"attached_condition": "t1.c1 <=> 'a' and t1.c1 = 'a'",
|
"using_index": true
|
},
|
"table": {
|
"table_name": "<derived2>",
|
"access_type": "ref",
|
"possible_keys": ["key0"],
|
"key": "key0",
|
"key_length": "8",
|
"used_key_parts": ["n1", "n2"],
|
"ref": ["j5.t1.n1", "j5.t1.n2"],
|
"rows": 2,
|
"filtered": 100,
|
"materialized": {
|
"query_block": {
|
"select_id": 2,
|
"table": {
|
"table_name": "t1",
|
"access_type": "ref",
|
"possible_keys": ["c1", "n1_c1_n2"],
|
"key": "n1_c1_n2",
|
"key_length": "4",
|
"used_key_parts": ["n1"],
|
"ref": ["j5.t1.n1"],
|
"rows": 1,
|
"filtered": 100,
|
"attached_condition": "t1.c1 = 'a'",
|
"using_index": true
|
}
|
}
|
}
|
}
|
}
|
}
|
If one is attentive enough, they can see that "materialized" node has a ref access in it which refers to "j5.t1.n1". Table "t1"
is outside the subquery, which means the subquery is using LATERAL DERIVED optimization.
But this is not at all obvious. EXPLAIN FORMAT=JSON should make it clear what optimization was used.