10.15.12 Example

10.15.12 Example

여기서는 테스트 슈트의 예제를 하나 사용합니다.

1#
2# Tracing of ORDER BY & GROUP BY simplification.
3#
4SET optimizer_trace="enabled=on",end_markers_in_json=on; # make readable
5SET optimizer_trace_max_mem_size=1000000; # avoid small default
6
7CREATE TABLE t1 (
8  pk INT, col_int_key INT,
9  col_varchar_key VARCHAR(1),
10  col_varchar_nokey VARCHAR(1)
11);
12
13INSERT INTO t1 VALUES
14  (10,7,'v','v'),(11,0,'s','s'),(12,9,'l','l'),(13,3,'y','y'),(14,4,'c','c'),
15  (15,2,'i','i'),(16,5,'h','h'),(17,3,'q','q'),(18,1,'a','a'),(19,3,'v','v'),
16  (20,6,'u','u'),(21,7,'s','s'),(22,5,'y','y'),(23,1,'z','z'),(24,204,'h','h'),
17  (25,224,'p','p'),(26,9,'e','e'),(27,5,'i','i'),(28,0,'y','y'),(29,3,'w','w');
18
19CREATE TABLE t2 (
20  pk INT, col_int_key INT,
21  col_varchar_key VARCHAR(1),
22  col_varchar_nokey VARCHAR(1),
23  PRIMARY KEY (pk)
24);
25
26INSERT INTO t2 VALUES
27  (1,4,'b','b'),(2,8,'y','y'),(3,0,'p','p'),(4,0,'f','f'),(5,0,'p','p'),
28  (6,7,'d','d'),(7,7,'f','f'),(8,5,'j','j'),(9,3,'e','e'),(10,188,'u','u'),
29  (11,4,'v','v'),(12,9,'u','u'),(13,6,'i','i'),(14,1,'x','x'),(15,5,'l','l'),
30  (16,6,'q','q'),(17,2,'n','n'),(18,4,'r','r'),(19,231,'c','c'),(20,4,'h','h'),
31  (21,3,'k','k'),(22,3,'t','t'),(23,7,'t','t'),(24,6,'k','k'),(25,7,'g','g'),
32  (26,9,'z','z'),(27,4,'n','n'),(28,4,'j','j'),(29,2,'l','l'),(30,1,'d','d'),
33  (31,2,'t','t'),(32,194,'y','y'),(33,2,'i','i'),(34,3,'j','j'),(35,8,'r','r'),
34  (36,4,'b','b'),(37,9,'o','o'),(38,4,'k','k'),(39,5,'a','a'),(40,5,'f','f'),
35  (41,9,'t','t'),(42,3,'c','c'),(43,8,'c','c'),(44,0,'r','r'),(45,98,'k','k'),
36  (46,3,'l','l'),(47,1,'o','o'),(48,0,'t','t'),(49,189,'v','v'),(50,8,'x','x'),
37  (51,3,'j','j'),(52,3,'x','x'),(53,9,'k','k'),(54,6,'o','o'),(55,8,'z','z'),
38  (56,3,'n','n'),(57,9,'c','c'),(58,5,'d','d'),(59,9,'s','s'),(60,2,'j','j'),
39  (61,2,'w','w'),(62,5,'f','f'),(63,8,'p','p'),(64,6,'o','o'),(65,9,'f','f'),
40  (66,0,'x','x'),(67,3,'q','q'),(68,6,'g','g'),(69,5,'x','x'),(70,8,'p','p'),
41  (71,2,'q','q'),(72,120,'q','q'),(73,25,'v','v'),(74,1,'g','g'),(75,3,'l','l'),
42  (76,1,'w','w'),(77,3,'h','h'),(78,153,'c','c'),(79,5,'o','o'),(80,9,'o','o'),
43  (81,1,'v','v'),(82,8,'y','y'),(83,7,'d','d'),(84,6,'p','p'),(85,2,'z','z'),
44  (86,4,'t','t'),(87,7,'b','b'),(88,3,'y','y'),(89,8,'k','k'),(90,4,'c','c'),
45  (91,6,'z','z'),(92,1,'t','t'),(93,7,'o','o'),(94,1,'u','u'),(95,0,'t','t'),
46  (96,2,'k','k'),(97,7,'u','u'),(98,2,'b','b'),(99,1,'m','m'),(100,5,'o','o');
47
48SELECT SUM(alias2.col_varchar_nokey) AS c1, alias2.pk AS c2
49  FROM t1 AS alias1
50  STRAIGHT_JOIN t2 AS alias2
51  ON alias2.pk = alias1.col_int_key
52  WHERE alias1.pk
53  GROUP BY c2
54  ORDER BY alias1.col_int_key, alias2.pk;
55
56+------+----+
57| c1   | c2 |
58+------+----+
59|    0 |  1 |
60|    0 |  2 |
61|    0 |  3 |
62|    0 |  4 |
63|    0 |  5 |
64|    0 |  6 |
65|    0 |  7 |
66|    0 |  9 |
67+------+----+

참고

참고를 위해, 이 section의 끝부분에 전체 trace를 끊김 없이 보여 줍니다.

이제 trace를 살펴볼 수 있습니다. 첫 번째 column (QUERY)에는 trace 대상이 되는 원래의 statement가 들어 있으며, 다음에 보이는 것과 같습니다:

1SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE\G
2*************************** 1. row ***************************
3QUERY: SELECT SUM(alias2.col_varchar_nokey) AS c1, alias2.pk AS c2
4  FROM t1 AS alias1
5  STRAIGHT_JOIN t2 AS alias2
6  ON alias2.pk = alias1.col_int_key
7  WHERE alias1.pk
8  GROUP BY c2
9  ORDER BY alias1.col_int_key, alias2.pk

여러 개의 trace가 저장된 경우, 이는 유용한 표시가 될 수 있습니다.

TRACE column은 먼저, statement의 실행이 다음과 같이 개별 단계들로 구성되어 있음을 보여 줍니다:

1"steps": [\
2  {\

이후, trace 대상 statement 내의 첫 번째 (그리고 유일한) SELECT에 대한 join 준비 과정이 다음과 같이 이어집니다:

1"steps": [\
2  {\
3    "expanded_query": "/* select#1 */ select \
4       sum(`test`.`alias2`.`col_varchar_nokey`) AS \
5       `SUM(alias2.col_varchar_nokey)`,`test`.`alias2`.`pk` AS `field2` \
6       from (`test`.`t1` `alias1` straight_join `test`.`t2` `alias2` \
7       on((`test`.`alias2`.`pk` = `test`.`alias1`.`col_int_key`))) \
8       where `test`.`alias1`.`pk` \
9       group by `test`.`alias2`.`pk` \
10       order by `test`.`alias1`.`col_int_key`,`test`.`alias2`.`pk`"\
11   }\
12] /* steps */\
13       } /* join_preparation */\
14     },\

방금 출력된 내용은 join을 준비할 때 사용되는 query를 보여 줍니다. 모든 column(field)은 각자의 데이터베이스와 테이블로 resolve되었으며, 각 SELECT에는 서브쿼리를 분석할 때 유용한 sequence number가 annotation으로 붙어 있습니다.

trace의 다음 부분은 condition 처리부터 시작하여 join이 어떻게 최적화되는지를 보여 줍니다:

1     {\
2      "join_optimization": {\
3               "select#": 1,\
4               "steps": [\
5                 {\
6                   "condition_processing": {\
7                     "condition": "WHERE",\
8                     "original_condition": "(`test`.`alias1`.`pk` and \
9                     (`test`.`alias2`.`pk` = `test`.`alias1`.`col_int_key`))",\
10                     "steps": [\
11                       {\
12                         "transformation": "equality_propagation",\
13                         "resulting_condition": "(`test`.`alias1`.`pk` and \
14                         multiple equal(`test`.`alias2`.`pk`, \
15                         `test`.`alias1`.`col_int_key`))"\
16                       },\
17                       {\
18                         "transformation": "constant_propagation",\
19                         "resulting_condition": "(`test`.`alias1`.`pk` and \
20                         multiple equal(`test`.`alias2`.`pk`, \
21                         `test`.`alias1`.`col_int_key`))"\
22                       },\
23                       {\
24                         "transformation": "trivial_condition_removal",\
25                         "resulting_condition": "(`test`.`alias1`.`pk` and \
26                         multiple equal(`test`.`alias2`.`pk`, \
27                         `test`.`alias1`.`col_int_key`))"\
28                 }\
29               ] /* steps */\
30             } /* condition_processing */\
31           },\

다음으로, 옵티마이저는 가능한 ref 액세스를 검사하고, 하나를 식별합니다:

1           {\
2             "ref_optimizer_key_uses": [\
3               {\
4                 "database": "test",\
5                 "table": "alias2",\
6                 "field": "pk",\
7                 "equals": "`test`.`alias1`.`col_int_key`",\
8                 "null_rejecting": true\
9              }\
10           ] /* ref_optimizer_key_uses */\
11         },\

NULL을 리젝트하는 ref 액세스가 식별되었습니다. 즉, test.alias1.col_int_key의 NULL은 어떤 값과도 매치될 수 없습니다. (연산자가 널 세이프 이퀄스 <=>였다면 매치될 수 있었음을 유의하십시오.)

다음으로, 쿼리 내의 각 테이블에 대해 테이블 스캔 또는 레인지 액세스의 비용과, 그에 의해 반환되는 레코드 수를 추정합니다.

테이블의 최적 순서를 찾아야 합니다. 일반적으로 그리디 서치가 사용되지만, statement에서 스트레이트 조인을 사용하므로 요청된 순서만 탐색되며, 하나 이상의 액세스 메서드가 선택됩니다. trace의 다음 부분에 표시된 대로, 테이블 스캔을 선택할 수 있습니다:

1           {\
2"records_estimation": [\
3               {\
4                 "database": "test",\
5                 "table": "alias1",\
6                 "const_keys_added": {\
7                   "keys": [\
8                   ] /* keys */,\
9                   "cause": "group_by"\
10                 } /* const_keys_added */,\
11                 "range_analysis": {\
12                   "table_scan": {\
13                     "records": 20,\
14                     "cost": 8.1977\
15                   } /* table_scan */\
16                 } /* range_analysis */\
17               },\
18               {\
19                 "database": "test",\
20                 "table": "alias2",\
21                 "const_keys_added": {\
22                   "keys": [\
23                     "PRIMARY"\
24                   ] /* keys */,\
25                   "cause": "group_by"\
26                 } /* const_keys_added */,\
27                 "range_analysis": {\
28                   "table_scan": {\
29                     "records": 100,\
30                     "cost": 24.588\
31                   } /* table_scan */,\
32                   "potential_range_indices": [\
33                     {\
34                       "index": "PRIMARY",\
35                       "usable": true,\
36                       "key_parts": [\
37                         "pk"\
38                       ] /* key_parts */\
39                     }\
40                   ] /* potential_range_indices */,\
41                   "setup_range_conditions": [\
42                   ] /* setup_range_conditions */,\
43                   "group_index_range": {\
44                     "chosen": false,\
45                     "cause": "not_single_table"\
46                      } /* group_index_range */\
47                } /* range_analysis */\
48               }\
49             ] /* records_estimation */\
50           },\

방금 range analysis의 두 번째 부분에서 본 것처럼, GROUP_MIN_MAX는 하나의 테이블만 허용하는데 조인에는 두 개의 테이블이 있기 때문에 사용할 수 없습니다. 이는 어떤 레인지 액세스도 가능하지 않음을 의미합니다.

옵티마이저는 첫 번째 테이블을 읽고, 여기에 필요한 condition을 적용하면 20개의 row가 생성된다고 추정합니다:

1           {\
2"considered_execution_plans": [\
3               {\
4                 "database": "test",\
5                 "table": "alias1",\
6                 "best_access_path": {\
7                   "considered_access_paths": [\
8                     {\
9                       "access_type": "scan",\
10                       "records": 20,\
11                       "cost": 2.0977,\
12                       "chosen": true\
13                     }\
14                   ] /* considered_access_paths */\
15                 } /* best_access_path */,\
16                 "cost_for_plan": 6.0977,\
17                 "records_for_plan": 20,\

alias2의 경우, 테이블 스캔으로 반환되는 레코드 수(75)가 ref 액세스로 반환되는 레코드 수(1)보다 훨씬 많기 때문에, 다음과 같이 테이블 스캔이 아니라 프라이머리 키에 대한 ref 액세스를 선택합니다:

1                 "rest_of_plan": [\
2                   {\
3                     "database": "test",\
4                     "table": "alias2",\
5                     "best_access_path": {\
6                       "considered_access_paths": [\
7                         {\
8                           "access_type": "ref",\
9                           "index": "PRIMARY",\
10                           "records": 1,\
11                           "cost": 20.2,\
12                           "chosen": true\
13                         },\
14                         {\
15                           "access_type": "scan",\
16                           "using_join_cache": true,\
17                           "records": 75,\
18                           "cost": 7.4917,\
19                           "chosen": false\
20                         }\
21                       ] /* considered_access_paths */\
22                     } /* best_access_path */,\
23                     "cost_for_plan": 30.098,\
24                     "records_for_plan": 20,\
25                     "chosen": true\
26                   }\
27                 ] /* rest_of_plan */\
28               }\
29             ] /* considered_execution_plans */\
30           },\

테이블 순서가 고정되었으므로, 조인 트리 아래쪽으로 condition을 푸시다운할 수 있도록 WHERE condition을 일찍 테스트할 수 있는 조각들로 분할할 수 있습니다:

1           {\
2              "attaching_conditions_to_tables": {\
3                "original_condition": "((`test`.`alias2`.`pk` = \
4                `test`.`alias1`.`col_int_key`) and `test`.`alias1`.`pk`)",\
5                "attached_conditions_computation": [\
6                ] /* attached_conditions_computation */,\
7                "attached_conditions_summary": [\
8                  {\
9                    "database": "test",\
10                    "table": "alias1",\
11                    "attached": "(`test`.`alias1`.`pk` and \
12                    (`test`.`alias1`.`col_int_key` is not null))"\
13           },\

이 condition은 alias1의 row에 대해, alias2의 row를 읽지 않고도 테스트할 수 있습니다.

1                 {\
2                   "database": "test",\
3                   "table": "alias2",\
4                   "attached": null\
5                 }\
6               ] /* attached_conditions_summary */\
7             } /* attaching_conditions_to_tables */\
8           },\
9           {\

이제 ORDER BY를 단순화하려고 시도합니다:

1              "clause_processing": {\
2               "clause": "ORDER BY",\
3               "original_clause": "`test`.`alias1`.`col_int_key`,`test`.`alias2`.`pk`",\
4               "items": [\
5                 {\
6                   "item": "`test`.`alias1`.`col_int_key`"\
7                 },\
8                 {\
9                   "item": "`test`.`alias2`.`pk`",\
10                   "eq_ref_to_preceding_items": true\
11                 }\
12               ] /* items */,\

WHERE clause에 alias2.pk=alias1.col_int_key가 포함되어 있기 때문에, 두 column 모두로 정렬하는 것은 불필요합니다. 두 번째 column은 항상 첫 번째 column과 같으므로, 첫 번째 column만으로 정렬할 수 있습니다.

1               "resulting_clause_is_simple": true,\
2               "resulting_clause": "`test`.`alias1`.`col_int_key`"\
3              } /* clause_processing */\
4           },\

짧아진 ORDER BY clause는 (이는 EXPLAIN 출력에서는 보이지 않지만) 하나의 테이블에 대한 단일 column만 사용하므로 인덱스 스캔으로 구현될 수 있습니다.

1           {\
2             "clause_processing": {\
3               "clause": "GROUP BY",\
4               "original_clause": "`test`.`alias2`.`pk`",\
5               "items": [\
6                 {\
7                   "item": "`test`.`alias2`.`pk`"\
8                 }\
9               ] /* items */,\
10               "resulting_clause_is_simple": false,\
11               "resulting_clause": "`test`.`alias2`.`pk`"\
12             } /* clause_processing */\
13           },\
14           {\
15             "refine_plan": [\
16               {\
17                 "database": "test",\
18                 "table": "alias1",\
19                 "scan_type": "table"\
20               },\
21               {\
22                 "database": "test",\
23                 "table": "alias2"\
24               }\
25             ] /* refine_plan */\
26           }\
27         ] /* steps */\
28       } /* join_optimization */\
29     },\
30     {\

이제 조인이 실행됩니다:

1       "join_execution": {\
2         "select#": 1,\
3         "steps": [\
4         ] /* steps */\
5       } /* join_execution */\
6     }\
7   ] /* steps */
8 }	0	0

모든 trace는 동일한 기본 구조를 가집니다. statement가 서브쿼리를 사용하면, 여러 번의 preparation, optimization, execution이 있을 수 있으며, 서브쿼리 전용 트랜스포메이션도 포함될 수 있습니다.

전체 trace는 다음과 같습니다:

1mysql> SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE\G
2*************************** 1. row ***************************
3                            QUERY: SELECT SUM(alias2.col_varchar_nokey) AS c1, alias2.pk AS c2
4  FROM t1 AS alias1
5  STRAIGHT_JOIN t2 AS alias2
6  ON alias2.pk = alias1.col_int_key
7  WHERE alias1.pk
8  GROUP BY c2
9  ORDER BY alias1.col_int_key, alias2.pk
10                            TRACE: {
11  "steps": [\
12    {\
13      "join_preparation": {\
14        "select#": 1,\
15        "steps": [\
16          {\
17            "expanded_query": "/* select#1 */ select sum(`alias2`.`col_varchar_nokey`) AS `c1`,`alias2`.`pk` AS `c2` from (`t1` `alias1` straight_join `t2` `alias2` on((`alias2`.`pk` = `alias1`.`col_int_key`))) where (0 <> `alias1`.`pk`) group by `c2` order by `alias1`.`col_int_key`,`alias2`.`pk`"\
18          },\
19          {\
20            "transformations_to_nested_joins": {\
21              "transformations": [\
22                "JOIN_condition_to_WHERE",\
23                "parenthesis_removal"\
24              ] /* transformations */,\
25              "expanded_query": "/* select#1 */ select sum(`alias2`.`col_varchar_nokey`) AS `c1`,`alias2`.`pk` AS `c2` from `t1` `alias1` straight_join `t2` `alias2` where ((0 <> `alias1`.`pk`) and (`alias2`.`pk` = `alias1`.`col_int_key`)) group by `c2` order by `alias1`.`col_int_key`,`alias2`.`pk`"\
26            } /* transformations_to_nested_joins */\
27          },\
28          {\
29            "functional_dependencies_of_GROUP_columns": {\
30              "all_columns_of_table_map_bits": [\
31                1\
32              ] /* all_columns_of_table_map_bits */,\
33              "columns": [\
34                "test.alias2.pk",\
35                "test.alias1.col_int_key"\
36              ] /* columns */\
37            } /* functional_dependencies_of_GROUP_columns */\
38          }\
39        ] /* steps */\
40      } /* join_preparation */\
41    },\
42    {\
43      "join_optimization": {\
44        "select#": 1,\
45        "steps": [\
46          {\
47            "condition_processing": {\
48              "condition": "WHERE",\
49              "original_condition": "((0 <> `alias1`.`pk`) and (`alias2`.`pk` = `alias1`.`col_int_key`))",\
50              "steps": [\
51                {\
52                  "transformation": "equality_propagation",\
53                  "resulting_condition": "((0 <> `alias1`.`pk`) and multiple equal(`alias2`.`pk`, `alias1`.`col_int_key`))"\
54                },\
55                {\
56                  "transformation": "constant_propagation",\
57                  "resulting_condition": "((0 <> `alias1`.`pk`) and multiple equal(`alias2`.`pk`, `alias1`.`col_int_key`))"\
58                },\
59                {\
60                  "transformation": "trivial_condition_removal",\
61                  "resulting_condition": "((0 <> `alias1`.`pk`) and multiple equal(`alias2`.`pk`, `alias1`.`col_int_key`))"\
62                }\
63              ] /* steps */\
64            } /* condition_processing */\
65          },\
66          {\
67            "substitute_generated_columns": {\
68            } /* substitute_generated_columns */\
69          },\
70          {\
71            "table_dependencies": [\
72              {\
73                "table": "`t1` `alias1`",\
74                "row_may_be_null": false,\
75                "map_bit": 0,\
76                "depends_on_map_bits": [\
77                ] /* depends_on_map_bits */\
78              },\
79              {\
80                "table": "`t2` `alias2`",\
81                "row_may_be_null": false,\
82                "map_bit": 1,\
83                "depends_on_map_bits": [\
84                  0\
85                ] /* depends_on_map_bits */\
86              }\
87            ] /* table_dependencies */\
88          },\
89          {\
90            "ref_optimizer_key_uses": [\
91              {\
92                "table": "`t2` `alias2`",\
93                "field": "pk",\
94                "equals": "`alias1`.`col_int_key`",\
95                "null_rejecting": true\
96              }\
97            ] /* ref_optimizer_key_uses */\
98          },\
99          {\
100            "rows_estimation": [\
101              {\
102                "table": "`t1` `alias1`",\
103                "table_scan": {\
104                  "rows": 20,\
105                  "cost": 0.25\
106                } /* table_scan */\
107              },\
108              {\
109                "table": "`t2` `alias2`",\
110                "const_keys_added": {\
111                  "keys": [\
112                    "PRIMARY"\
113                  ] /* keys */,\
114                  "cause": "group_by"\
115                } /* const_keys_added */,\
116                "range_analysis": {\
117                  "table_scan": {\
118                    "rows": 100,\
119                    "cost": 12.35\
120                  } /* table_scan */,\
121                  "potential_range_indexes": [\
122                    {\
123                      "index": "PRIMARY",\
124                      "usable": true,\
125                      "key_parts": [\
126                        "pk"\
127                      ] /* key_parts */\
128                    }\
129                  ] /* potential_range_indexes */,\
130                  "setup_range_conditions": [\
131                  ] /* setup_range_conditions */,\
132                  "group_index_skip_scan": {\
133                    "chosen": false,\
134                    "cause": "not_single_table"\
135                  } /* group_index_skip_scan */,\
136                  "skip_scan_range": {\
137                    "chosen": false,\
138                    "cause": "not_single_table"\
139                  } /* skip_scan_range */\
140                } /* range_analysis */\
141              }\
142            ] /* rows_estimation */\
143          },\
144          {\
145            "considered_execution_plans": [\
146              {\
147                "plan_prefix": [\
148                ] /* plan_prefix */,\
149                "table": "`t1` `alias1`",\
150                "best_access_path": {\
151                  "considered_access_paths": [\
152                    {\
153                      "rows_to_scan": 20,\
154                      "filtering_effect": [\
155                      ] /* filtering_effect */,\
156                      "final_filtering_effect": 0.9,\
157                      "access_type": "scan",\
158                      "resulting_rows": 18,\
159                      "cost": 2.25,\
160                      "chosen": true\
161                    }\
162                  ] /* considered_access_paths */\
163                } /* best_access_path */,\
164                "condition_filtering_pct": 100,\
165                "rows_for_plan": 18,\
166                "cost_for_plan": 2.25,\
167                "rest_of_plan": [\
168                  {\
169                    "plan_prefix": [\
170                      "`t1` `alias1`"\
171                    ] /* plan_prefix */,\
172                    "table": "`t2` `alias2`",\
173                    "best_access_path": {\
174                      "considered_access_paths": [\
175                        {\
176                          "access_type": "eq_ref",\
177                          "index": "PRIMARY",\
178                          "rows": 1,\
179                          "cost": 6.3,\
180                          "chosen": true,\
181                          "cause": "clustered_pk_chosen_by_heuristics"\
182                        },\
183                        {\
184                          "rows_to_scan": 100,\
185                          "filtering_effect": [\
186                          ] /* filtering_effect */,\
187                          "final_filtering_effect": 1,\
188                          "access_type": "scan",\
189                          "using_join_cache": true,\
190                          "buffers_needed": 1,\
191                          "resulting_rows": 100,\
192                          "cost": 180.25,\
193                          "chosen": false\
194                        }\
195                      ] /* considered_access_paths */\
196                    } /* best_access_path */,\
197                    "condition_filtering_pct": 100,\
198                    "rows_for_plan": 18,\
199                    "cost_for_plan": 8.55,\
200                    "chosen": true\
201                  }\
202                ] /* rest_of_plan */\
203              }\
204            ] /* considered_execution_plans */\
205          },\
206          {\
207            "attaching_conditions_to_tables": {\
208              "original_condition": "((`alias2`.`pk` = `alias1`.`col_int_key`) and (0 <> `alias1`.`pk`))",\
209              "attached_conditions_computation": [\
210              ] /* attached_conditions_computation */,\
211              "attached_conditions_summary": [\
212                {\
213                  "table": "`t1` `alias1`",\
214                  "attached": "((0 <> `alias1`.`pk`) and (`alias1`.`col_int_key` is not null))"\
215                },\
216                {\
217                  "table": "`t2` `alias2`",\
218                  "attached": "(`alias2`.`pk` = `alias1`.`col_int_key`)"\
219                }\
220              ] /* attached_conditions_summary */\
221            } /* attaching_conditions_to_tables */\
222          },\
223          {\
224            "optimizing_distinct_group_by_order_by": {\
225              "simplifying_order_by": {\
226                "original_clause": "`alias1`.`col_int_key`,`alias2`.`pk`",\
227                "items": [\
228                  {\
229                    "item": "`alias1`.`col_int_key`"\
230                  },\
231                  {\
232                    "item": "`alias2`.`pk`",\
233                    "eq_ref_to_preceding_items": true\
234                  }\
235                ] /* items */,\
236                "resulting_clause_is_simple": true,\
237                "resulting_clause": "`alias1`.`col_int_key`"\
238              } /* simplifying_order_by */,\
239              "simplifying_group_by": {\
240                "original_clause": "`c2`",\
241                "items": [\
242                  {\
243                    "item": "`alias2`.`pk`"\
244                  }\
245                ] /* items */,\
246                "resulting_clause_is_simple": false,\
247                "resulting_clause": "`c2`"\
248              } /* simplifying_group_by */\
249            } /* optimizing_distinct_group_by_order_by */\
250          },\
251          {\
252            "finalizing_table_conditions": [\
253              {\
254                "table": "`t1` `alias1`",\
255                "original_table_condition": "((0 <> `alias1`.`pk`) and (`alias1`.`col_int_key` is not null))",\
256                "final_table_condition   ": "((0 <> `alias1`.`pk`) and (`alias1`.`col_int_key` is not null))"\
257              },\
258              {\
259                "table": "`t2` `alias2`",\
260                "original_table_condition": "(`alias2`.`pk` = `alias1`.`col_int_key`)",\
261                "final_table_condition   ": null\
262              }\
263            ] /* finalizing_table_conditions */\
264          },\
265          {\
266            "refine_plan": [\
267              {\
268                "table": "`t1` `alias1`"\
269              },\
270              {\
271                "table": "`t2` `alias2`"\
272              }\
273            ] /* refine_plan */\
274          },\
275          {\
276            "considering_tmp_tables": [\
277              {\
278                "adding_tmp_table_in_plan_at_position": 2,\
279                "write_method": "continuously_update_group_row"\
280              },\
281              {\
282                "adding_sort_to_table": ""\
283              } /* filesort */\
284            ] /* considering_tmp_tables */\
285          }\
286        ] /* steps */\
287      } /* join_optimization */\
288    },\
289    {\
290      "join_execution": {\
291        "select#": 1,\
292        "steps": [\
293          {\
294            "temp_table_aggregate": {\
295              "select#": 1,\
296              "steps": [\
297                {\
298                  "creating_tmp_table": {\
299                    "tmp_table_info": {\
300                      "table": "<temporary>",\
301                      "in_plan_at_position": 2,\
302                      "columns": 3,\
303                      "row_length": 18,\
304                      "key_length": 4,\
305                      "unique_constraint": false,\
306                      "makes_grouped_rows": true,\
307                      "cannot_insert_duplicates": false,\
308                      "location": "TempTable"\
309                    } /* tmp_table_info */\
310                  } /* creating_tmp_table */\
311                }\
312              ] /* steps */\
313            } /* temp_table_aggregate */\
314          },\
315          {\
316            "sorting_table": "<temporary>",\
317            "filesort_information": [\
318              {\
319                "direction": "asc",\
320                "expression": "`alias1`.`col_int_key`"\
321              }\
322            ] /* filesort_information */,\
323            "filesort_priority_queue_optimization": {\
324              "usable": false,\
325              "cause": "not applicable (no LIMIT)"\
326            } /* filesort_priority_queue_optimization */,\
327            "filesort_execution": [\
328            ] /* filesort_execution */,\
329            "filesort_summary": {\
330              "memory_available": 262144,\
331              "key_size": 9,\
332              "row_size": 26,\
333              "max_rows_per_buffer": 7710,\
334              "num_rows_estimate": 18446744073709551615,\
335              "num_rows_found": 8,\
336              "num_initial_chunks_spilled_to_disk": 0,\
337              "peak_memory_used": 32832,\
338              "sort_algorithm": "std::sort",\
339              "unpacked_addon_fields": "skip_heuristic",\
340              "sort_mode": "<fixed_sort_key, additional_fields>"\
341            } /* filesort_summary */\
342          }\
343        ] /* steps */\
344      } /* join_execution */\
345    }\
346  ] /* steps */
347}
348MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0
349          INSUFFICIENT_PRIVILEGES: 0