1336 lines
42 KiB
Plaintext
1336 lines
42 KiB
Plaintext
SET optimizer_trace_max_mem_size=1048576;
|
|
SET optimizer_trace="enabled=on,one_line=off";
|
|
SET end_markers_in_json="on";
|
|
SET max_length_for_sort_data=1024;
|
|
CREATE TABLE tmp (
|
|
pk INT PRIMARY KEY AUTO_INCREMENT,
|
|
col1 CHAR (1)
|
|
);
|
|
INSERT INTO tmp(col1) VALUES ('a'),('b'),('c'),('d'),('e'),('f'),('g'),('h');
|
|
CREATE TABLE t1 (
|
|
uniq VARCHAR(10),
|
|
col1 VARCHAR(10),
|
|
col2 VARCHAR(1024)
|
|
) CHARSET utf8mb4;
|
|
INSERT INTO t1 SELECT pk, col1, col1 FROM tmp;
|
|
SELECT uniq, col1 FROM t1 GROUP BY col2,uniq LIMIT 3;
|
|
uniq col1
|
|
1 a
|
|
2 b
|
|
3 c
|
|
SELECT * FROM information_schema.OPTIMIZER_TRACE;
|
|
QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
|
|
SELECT uniq, col1 FROM t1 GROUP BY col2,uniq LIMIT 3 {
|
|
"steps": [
|
|
{
|
|
"join_preparation": {
|
|
"select#": 1,
|
|
"steps": [
|
|
{
|
|
"expanded_query": "/* select#1 */ select `t1`.`uniq` AS `uniq`,`t1`.`col1` AS `col1` from `t1` group by `t1`.`col2`,`t1`.`uniq` limit 3"
|
|
}
|
|
] /* steps */
|
|
} /* join_preparation */
|
|
},
|
|
{
|
|
"join_optimization": {
|
|
"select#": 1,
|
|
"steps": [
|
|
{
|
|
"substitute_generated_columns": {
|
|
} /* substitute_generated_columns */
|
|
},
|
|
{
|
|
"table_dependencies": [
|
|
{
|
|
"table": "`t1`",
|
|
"row_may_be_null": false,
|
|
"map_bit": 0,
|
|
"depends_on_map_bits": [
|
|
] /* depends_on_map_bits */
|
|
}
|
|
] /* table_dependencies */
|
|
},
|
|
{
|
|
"rows_estimation": [
|
|
{
|
|
"table": "`t1`",
|
|
"table_scan": {
|
|
"rows": 8,
|
|
"cost": 0.25
|
|
} /* table_scan */
|
|
}
|
|
] /* rows_estimation */
|
|
},
|
|
{
|
|
"considered_execution_plans": [
|
|
{
|
|
"plan_prefix": [
|
|
] /* plan_prefix */,
|
|
"table": "`t1`",
|
|
"best_access_path": {
|
|
"considered_access_paths": [
|
|
{
|
|
"rows_to_scan": 8,
|
|
"access_type": "scan",
|
|
"resulting_rows": 8,
|
|
"cost": 1.05,
|
|
"chosen": true
|
|
}
|
|
] /* considered_access_paths */
|
|
} /* best_access_path */,
|
|
"condition_filtering_pct": 100,
|
|
"rows_for_plan": 8,
|
|
"cost_for_plan": 1.05,
|
|
"chosen": true
|
|
}
|
|
] /* considered_execution_plans */
|
|
},
|
|
{
|
|
"attaching_conditions_to_tables": {
|
|
"original_condition": null,
|
|
"attached_conditions_computation": [
|
|
] /* attached_conditions_computation */,
|
|
"attached_conditions_summary": [
|
|
{
|
|
"table": "`t1`",
|
|
"attached": null
|
|
}
|
|
] /* attached_conditions_summary */
|
|
} /* attaching_conditions_to_tables */
|
|
},
|
|
{
|
|
"optimizing_distinct_group_by_order_by": {
|
|
"simplifying_group_by": {
|
|
"original_clause": "`t1`.`col2`,`t1`.`uniq`",
|
|
"items": [
|
|
{
|
|
"item": "`t1`.`col2`"
|
|
},
|
|
{
|
|
"item": "`t1`.`uniq`"
|
|
}
|
|
] /* items */,
|
|
"resulting_clause_is_simple": true,
|
|
"resulting_clause": "`t1`.`col2`,`t1`.`uniq`"
|
|
} /* simplifying_group_by */
|
|
} /* optimizing_distinct_group_by_order_by */
|
|
},
|
|
{
|
|
"finalizing_table_conditions": [
|
|
] /* finalizing_table_conditions */
|
|
},
|
|
{
|
|
"refine_plan": [
|
|
{
|
|
"table": "`t1`"
|
|
}
|
|
] /* refine_plan */
|
|
},
|
|
{
|
|
"considering_tmp_tables": [
|
|
{
|
|
"adding_tmp_table_in_plan_at_position": 1,
|
|
"write_method": "write_all_rows"
|
|
}
|
|
] /* considering_tmp_tables */
|
|
}
|
|
] /* steps */
|
|
} /* join_optimization */
|
|
},
|
|
{
|
|
"join_execution": {
|
|
"select#": 1,
|
|
"steps": [
|
|
{
|
|
"creating_tmp_table": {
|
|
"tmp_table_info": {
|
|
"in_plan_at_position": 1,
|
|
"columns": 4,
|
|
"row_length": 4190,
|
|
"key_length": 8,
|
|
"unique_constraint": true,
|
|
"makes_grouped_rows": true,
|
|
"cannot_insert_duplicates": false,
|
|
"location": "TempTable"
|
|
} /* tmp_table_info */
|
|
} /* creating_tmp_table */
|
|
},
|
|
{
|
|
"materialize": {
|
|
"select#": 1,
|
|
"steps": [
|
|
] /* steps */
|
|
} /* materialize */
|
|
}
|
|
] /* steps */
|
|
} /* join_execution */
|
|
}
|
|
] /* steps */
|
|
} 0 0
|
|
SELECT uniq, col1, col2 FROM t1 GROUP BY uniq ;
|
|
uniq col1 col2
|
|
1 a a
|
|
2 b b
|
|
3 c c
|
|
4 d d
|
|
5 e e
|
|
6 f f
|
|
7 g g
|
|
8 h h
|
|
SELECT * FROM information_schema.OPTIMIZER_TRACE;
|
|
QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
|
|
SELECT uniq, col1, col2 FROM t1 GROUP BY uniq {
|
|
"steps": [
|
|
{
|
|
"join_preparation": {
|
|
"select#": 1,
|
|
"steps": [
|
|
{
|
|
"expanded_query": "/* select#1 */ select `t1`.`uniq` AS `uniq`,`t1`.`col1` AS `col1`,`t1`.`col2` AS `col2` from `t1` group by `t1`.`uniq`"
|
|
}
|
|
] /* steps */
|
|
} /* join_preparation */
|
|
},
|
|
{
|
|
"join_optimization": {
|
|
"select#": 1,
|
|
"steps": [
|
|
{
|
|
"substitute_generated_columns": {
|
|
} /* substitute_generated_columns */
|
|
},
|
|
{
|
|
"table_dependencies": [
|
|
{
|
|
"table": "`t1`",
|
|
"row_may_be_null": false,
|
|
"map_bit": 0,
|
|
"depends_on_map_bits": [
|
|
] /* depends_on_map_bits */
|
|
}
|
|
] /* table_dependencies */
|
|
},
|
|
{
|
|
"rows_estimation": [
|
|
{
|
|
"table": "`t1`",
|
|
"table_scan": {
|
|
"rows": 8,
|
|
"cost": 0.25
|
|
} /* table_scan */
|
|
}
|
|
] /* rows_estimation */
|
|
},
|
|
{
|
|
"considered_execution_plans": [
|
|
{
|
|
"plan_prefix": [
|
|
] /* plan_prefix */,
|
|
"table": "`t1`",
|
|
"best_access_path": {
|
|
"considered_access_paths": [
|
|
{
|
|
"rows_to_scan": 8,
|
|
"access_type": "scan",
|
|
"resulting_rows": 8,
|
|
"cost": 1.05,
|
|
"chosen": true,
|
|
"use_tmp_table": true
|
|
}
|
|
] /* considered_access_paths */
|
|
} /* best_access_path */,
|
|
"condition_filtering_pct": 100,
|
|
"rows_for_plan": 8,
|
|
"cost_for_plan": 1.05,
|
|
"sort_cost": 8,
|
|
"new_cost_for_plan": 9.05,
|
|
"chosen": true
|
|
}
|
|
] /* considered_execution_plans */
|
|
},
|
|
{
|
|
"attaching_conditions_to_tables": {
|
|
"original_condition": null,
|
|
"attached_conditions_computation": [
|
|
] /* attached_conditions_computation */,
|
|
"attached_conditions_summary": [
|
|
{
|
|
"table": "`t1`",
|
|
"attached": null
|
|
}
|
|
] /* attached_conditions_summary */
|
|
} /* attaching_conditions_to_tables */
|
|
},
|
|
{
|
|
"optimizing_distinct_group_by_order_by": {
|
|
"simplifying_group_by": {
|
|
"original_clause": "`t1`.`uniq`",
|
|
"items": [
|
|
{
|
|
"item": "`t1`.`uniq`"
|
|
}
|
|
] /* items */,
|
|
"resulting_clause_is_simple": true,
|
|
"resulting_clause": "`t1`.`uniq`"
|
|
} /* simplifying_group_by */
|
|
} /* optimizing_distinct_group_by_order_by */
|
|
},
|
|
{
|
|
"finalizing_table_conditions": [
|
|
] /* finalizing_table_conditions */
|
|
},
|
|
{
|
|
"refine_plan": [
|
|
{
|
|
"table": "`t1`"
|
|
}
|
|
] /* refine_plan */
|
|
},
|
|
{
|
|
"considering_tmp_tables": [
|
|
{
|
|
"adding_tmp_table_in_plan_at_position": 1,
|
|
"write_method": "write_all_rows"
|
|
}
|
|
] /* considering_tmp_tables */
|
|
}
|
|
] /* steps */
|
|
} /* join_optimization */
|
|
},
|
|
{
|
|
"join_execution": {
|
|
"select#": 1,
|
|
"steps": [
|
|
{
|
|
"creating_tmp_table": {
|
|
"tmp_table_info": {
|
|
"in_plan_at_position": 1,
|
|
"columns": 3,
|
|
"row_length": 4182,
|
|
"key_length": 43,
|
|
"unique_constraint": false,
|
|
"makes_grouped_rows": true,
|
|
"cannot_insert_duplicates": false,
|
|
"location": "TempTable"
|
|
} /* tmp_table_info */
|
|
} /* creating_tmp_table */
|
|
},
|
|
{
|
|
"materialize": {
|
|
"select#": 1,
|
|
"steps": [
|
|
] /* steps */
|
|
} /* materialize */
|
|
}
|
|
] /* steps */
|
|
} /* join_execution */
|
|
}
|
|
] /* steps */
|
|
} 0 0
|
|
select @@tmp_table_size;
|
|
@@tmp_table_size
|
|
16777216
|
|
SET @old_size= @@tmp_table_size;
|
|
SET SESSION tmp_table_size= 1024;
|
|
SET SESSION internal_tmp_mem_storage_engine='memory';
|
|
INSERT INTO t1 SELECT pk+8, col1, col1 FROM tmp;
|
|
SELECT uniq, col1, col2 FROM t1 GROUP BY uniq;
|
|
uniq col1 col2
|
|
1 a a
|
|
10 b b
|
|
11 c c
|
|
12 d d
|
|
13 e e
|
|
14 f f
|
|
15 g g
|
|
16 h h
|
|
2 b b
|
|
3 c c
|
|
4 d d
|
|
5 e e
|
|
6 f f
|
|
7 g g
|
|
8 h h
|
|
9 a a
|
|
SELECT * FROM information_schema.OPTIMIZER_TRACE;
|
|
QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
|
|
SELECT uniq, col1, col2 FROM t1 GROUP BY uniq {
|
|
"steps": [
|
|
{
|
|
"join_preparation": {
|
|
"select#": 1,
|
|
"steps": [
|
|
{
|
|
"expanded_query": "/* select#1 */ select `t1`.`uniq` AS `uniq`,`t1`.`col1` AS `col1`,`t1`.`col2` AS `col2` from `t1` group by `t1`.`uniq`"
|
|
}
|
|
] /* steps */
|
|
} /* join_preparation */
|
|
},
|
|
{
|
|
"join_optimization": {
|
|
"select#": 1,
|
|
"steps": [
|
|
{
|
|
"substitute_generated_columns": {
|
|
} /* substitute_generated_columns */
|
|
},
|
|
{
|
|
"table_dependencies": [
|
|
{
|
|
"table": "`t1`",
|
|
"row_may_be_null": false,
|
|
"map_bit": 0,
|
|
"depends_on_map_bits": [
|
|
] /* depends_on_map_bits */
|
|
}
|
|
] /* table_dependencies */
|
|
},
|
|
{
|
|
"rows_estimation": [
|
|
{
|
|
"table": "`t1`",
|
|
"table_scan": {
|
|
"rows": 16,
|
|
"cost": 0.25
|
|
} /* table_scan */
|
|
}
|
|
] /* rows_estimation */
|
|
},
|
|
{
|
|
"considered_execution_plans": [
|
|
{
|
|
"plan_prefix": [
|
|
] /* plan_prefix */,
|
|
"table": "`t1`",
|
|
"best_access_path": {
|
|
"considered_access_paths": [
|
|
{
|
|
"rows_to_scan": 16,
|
|
"access_type": "scan",
|
|
"resulting_rows": 16,
|
|
"cost": 1.85,
|
|
"chosen": true,
|
|
"use_tmp_table": true
|
|
}
|
|
] /* considered_access_paths */
|
|
} /* best_access_path */,
|
|
"condition_filtering_pct": 100,
|
|
"rows_for_plan": 16,
|
|
"cost_for_plan": 1.85,
|
|
"sort_cost": 16,
|
|
"new_cost_for_plan": 17.85,
|
|
"chosen": true
|
|
}
|
|
] /* considered_execution_plans */
|
|
},
|
|
{
|
|
"attaching_conditions_to_tables": {
|
|
"original_condition": null,
|
|
"attached_conditions_computation": [
|
|
] /* attached_conditions_computation */,
|
|
"attached_conditions_summary": [
|
|
{
|
|
"table": "`t1`",
|
|
"attached": null
|
|
}
|
|
] /* attached_conditions_summary */
|
|
} /* attaching_conditions_to_tables */
|
|
},
|
|
{
|
|
"optimizing_distinct_group_by_order_by": {
|
|
"simplifying_group_by": {
|
|
"original_clause": "`t1`.`uniq`",
|
|
"items": [
|
|
{
|
|
"item": "`t1`.`uniq`"
|
|
}
|
|
] /* items */,
|
|
"resulting_clause_is_simple": true,
|
|
"resulting_clause": "`t1`.`uniq`"
|
|
} /* simplifying_group_by */
|
|
} /* optimizing_distinct_group_by_order_by */
|
|
},
|
|
{
|
|
"finalizing_table_conditions": [
|
|
] /* finalizing_table_conditions */
|
|
},
|
|
{
|
|
"refine_plan": [
|
|
{
|
|
"table": "`t1`"
|
|
}
|
|
] /* refine_plan */
|
|
},
|
|
{
|
|
"considering_tmp_tables": [
|
|
{
|
|
"adding_tmp_table_in_plan_at_position": 1,
|
|
"write_method": "write_all_rows"
|
|
}
|
|
] /* considering_tmp_tables */
|
|
}
|
|
] /* steps */
|
|
} /* join_optimization */
|
|
},
|
|
{
|
|
"join_execution": {
|
|
"select#": 1,
|
|
"steps": [
|
|
{
|
|
"creating_tmp_table": {
|
|
"tmp_table_info": {
|
|
"in_plan_at_position": 1,
|
|
"columns": 3,
|
|
"row_length": 4182,
|
|
"key_length": 43,
|
|
"unique_constraint": false,
|
|
"makes_grouped_rows": true,
|
|
"cannot_insert_duplicates": false,
|
|
"location": "memory (heap)",
|
|
"row_limit_estimate": 1
|
|
} /* tmp_table_info */
|
|
} /* creating_tmp_table */
|
|
},
|
|
{
|
|
"materialize": {
|
|
"select#": 1,
|
|
"steps": [
|
|
{
|
|
"converting_tmp_table_to_ondisk": {
|
|
"cause": "memory_table_size_exceeded",
|
|
"tmp_table_info": {
|
|
"in_plan_at_position": 1,
|
|
"columns": 3,
|
|
"row_length": 4182,
|
|
"key_length": 43,
|
|
"unique_constraint": false,
|
|
"makes_grouped_rows": true,
|
|
"cannot_insert_duplicates": false,
|
|
"location": "disk (TMP_TABLE_ENGINE)",
|
|
"record_format": "packed"
|
|
} /* tmp_table_info */
|
|
} /* converting_tmp_table_to_ondisk */
|
|
}
|
|
] /* steps */
|
|
} /* materialize */
|
|
}
|
|
] /* steps */
|
|
} /* join_execution */
|
|
}
|
|
] /* steps */
|
|
} 0 0
|
|
SET SESSION internal_tmp_mem_storage_engine=default;
|
|
SET GLOBAL tmp_table_size= @old_size;
|
|
SELECT pool_id FROM information_schema.INNODB_BUFFER_PAGE LIMIT 1;
|
|
pool_id
|
|
0
|
|
SELECT * FROM information_schema.OPTIMIZER_TRACE;
|
|
QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
|
|
SELECT pool_id FROM information_schema.INNODB_BUFFER_PAGE LIMIT 1 {
|
|
"steps": [
|
|
{
|
|
"creating_tmp_table": {
|
|
"tmp_table_info": {
|
|
"columns": 20,
|
|
"row_length": 6852,
|
|
"key_length": 0,
|
|
"unique_constraint": false,
|
|
"makes_grouped_rows": false,
|
|
"cannot_insert_duplicates": false,
|
|
"location": "memory (heap)",
|
|
"row_limit_estimate": 1
|
|
} /* tmp_table_info */
|
|
} /* creating_tmp_table */
|
|
},
|
|
{
|
|
"join_preparation": {
|
|
"select#": 1,
|
|
"steps": [
|
|
{
|
|
"expanded_query": "/* select#1 */ select `information_schema`.`INNODB_BUFFER_PAGE`.`POOL_ID` AS `pool_id` from `information_schema`.`INNODB_BUFFER_PAGE` limit 1"
|
|
}
|
|
] /* steps */
|
|
} /* join_preparation */
|
|
},
|
|
{
|
|
"join_optimization": {
|
|
"select#": 1,
|
|
"steps": [
|
|
{
|
|
"table_dependencies": [
|
|
{
|
|
"table": "`information_schema`.`INNODB_BUFFER_PAGE`",
|
|
"row_may_be_null": false,
|
|
"map_bit": 0,
|
|
"depends_on_map_bits": [
|
|
] /* depends_on_map_bits */
|
|
}
|
|
] /* table_dependencies */
|
|
},
|
|
{
|
|
"rows_estimation": [
|
|
{
|
|
"table": "`information_schema`.`INNODB_BUFFER_PAGE`",
|
|
"table_scan": {
|
|
"rows": 2,
|
|
"cost": 2.525
|
|
} /* table_scan */
|
|
}
|
|
] /* rows_estimation */
|
|
},
|
|
{
|
|
"considered_execution_plans": [
|
|
{
|
|
"plan_prefix": [
|
|
] /* plan_prefix */,
|
|
"table": "`information_schema`.`INNODB_BUFFER_PAGE`",
|
|
"best_access_path": {
|
|
"considered_access_paths": [
|
|
{
|
|
"rows_to_scan": 2,
|
|
"access_type": "scan",
|
|
"resulting_rows": 2,
|
|
"cost": 2.725,
|
|
"chosen": true
|
|
}
|
|
] /* considered_access_paths */
|
|
} /* best_access_path */,
|
|
"condition_filtering_pct": 100,
|
|
"rows_for_plan": 2,
|
|
"cost_for_plan": 2.725,
|
|
"chosen": true
|
|
}
|
|
] /* considered_execution_plans */
|
|
},
|
|
{
|
|
"attaching_conditions_to_tables": {
|
|
"original_condition": null,
|
|
"attached_conditions_computation": [
|
|
] /* attached_conditions_computation */,
|
|
"attached_conditions_summary": [
|
|
{
|
|
"table": "`information_schema`.`INNODB_BUFFER_PAGE`",
|
|
"attached": null
|
|
}
|
|
] /* attached_conditions_summary */
|
|
} /* attaching_conditions_to_tables */
|
|
},
|
|
{
|
|
"finalizing_table_conditions": [
|
|
] /* finalizing_table_conditions */
|
|
},
|
|
{
|
|
"refine_plan": [
|
|
{
|
|
"table": "`information_schema`.`INNODB_BUFFER_PAGE`"
|
|
}
|
|
] /* refine_plan */
|
|
}
|
|
] /* steps */
|
|
} /* join_optimization */
|
|
},
|
|
{
|
|
"join_execution": {
|
|
"select#": 1,
|
|
"steps": [
|
|
{
|
|
"converting_tmp_table_to_ondisk": {
|
|
"cause": "memory_table_size_exceeded",
|
|
"tmp_table_info": {
|
|
"table": "`information_schema`.`INNODB_BUFFER_PAGE`",
|
|
"in_plan_at_position": 0,
|
|
"columns": 20,
|
|
"row_length": 6852,
|
|
"key_length": 0,
|
|
"unique_constraint": false,
|
|
"makes_grouped_rows": false,
|
|
"cannot_insert_duplicates": false,
|
|
"location": "disk (TMP_TABLE_ENGINE)",
|
|
"record_format": "packed"
|
|
} /* tmp_table_info */
|
|
} /* converting_tmp_table_to_ondisk */
|
|
}
|
|
] /* steps */
|
|
} /* join_execution */
|
|
}
|
|
] /* steps */
|
|
} 0 0
|
|
DROP TABLE t1, tmp;
|
|
#
|
|
# Bug#17231940: THE OPTIMIZER STILL USES FIXED LENGTH TEMPORARY TABLES
|
|
# ON DISK
|
|
#
|
|
CREATE TABLE t1 (
|
|
c1 INT AUTO_INCREMENT PRIMARY KEY,
|
|
c2 VARCHAR(250)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
|
|
Warnings:
|
|
Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
|
|
INSERT INTO t1(c2) VALUES ('b'),('b');
|
|
INSERT INTO t1(c2) SELECT t1.c2 FROM t1, t1 t2, t1 t3, t1 t4, t1 t5, t1 t6;
|
|
SET @@max_heap_table_size=1;
|
|
Warnings:
|
|
Warning 1292 Truncated incorrect max_heap_table_size value: '1'
|
|
SET @@group_concat_max_len= 500;
|
|
SET SESSION internal_tmp_mem_storage_engine='memory';
|
|
SELECT c1,GROUP_CONCAT(c2) cc FROM
|
|
(SELECT * FROM t1 UNION SELECT c1, 'a' FROM t1) tt
|
|
GROUP BY c1
|
|
ORDER BY cc;
|
|
c1 cc
|
|
1 a,b
|
|
10 a,b
|
|
11 a,b
|
|
12 a,b
|
|
13 a,b
|
|
14 a,b
|
|
15 a,b
|
|
16 a,b
|
|
17 a,b
|
|
18 a,b
|
|
19 a,b
|
|
2 a,b
|
|
20 a,b
|
|
21 a,b
|
|
22 a,b
|
|
23 a,b
|
|
24 a,b
|
|
25 a,b
|
|
26 a,b
|
|
27 a,b
|
|
28 a,b
|
|
29 a,b
|
|
3 a,b
|
|
30 a,b
|
|
31 a,b
|
|
32 a,b
|
|
33 a,b
|
|
34 a,b
|
|
35 a,b
|
|
36 a,b
|
|
37 a,b
|
|
38 a,b
|
|
39 a,b
|
|
4 a,b
|
|
40 a,b
|
|
41 a,b
|
|
42 a,b
|
|
43 a,b
|
|
44 a,b
|
|
45 a,b
|
|
46 a,b
|
|
47 a,b
|
|
48 a,b
|
|
49 a,b
|
|
5 a,b
|
|
50 a,b
|
|
51 a,b
|
|
52 a,b
|
|
53 a,b
|
|
54 a,b
|
|
55 a,b
|
|
56 a,b
|
|
57 a,b
|
|
58 a,b
|
|
59 a,b
|
|
6 a,b
|
|
60 a,b
|
|
61 a,b
|
|
62 a,b
|
|
63 a,b
|
|
64 a,b
|
|
65 a,b
|
|
66 a,b
|
|
7 a,b
|
|
8 a,b
|
|
9 a,b
|
|
SELECT * FROM information_schema.OPTIMIZER_TRACE;
|
|
QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
|
|
SELECT c1,GROUP_CONCAT(c2) cc FROM
|
|
(SELECT * FROM t1 UNION SELECT c1, 'a' FROM t1) tt
|
|
GROUP BY c1
|
|
ORDER BY cc {
|
|
"steps": [
|
|
{
|
|
"join_preparation": {
|
|
"select#": 1,
|
|
"steps": [
|
|
{
|
|
"join_preparation": {
|
|
"select#": 2,
|
|
"steps": [
|
|
{
|
|
"expanded_query": "/* select#2 */ select `t1`.`c1` AS `c1`,`t1`.`c2` AS `c2` from `t1`"
|
|
}
|
|
] /* steps */
|
|
} /* join_preparation */
|
|
},
|
|
{
|
|
"join_preparation": {
|
|
"select#": 3,
|
|
"steps": [
|
|
{
|
|
"expanded_query": "/* select#3 */ select `t1`.`c1` AS `c1`,'a' AS `a` from `t1`"
|
|
}
|
|
] /* steps */
|
|
} /* join_preparation */
|
|
},
|
|
{
|
|
"creating_tmp_table": {
|
|
"tmp_table_info": {
|
|
"table": "intermediate_tmp_table",
|
|
"columns": 2,
|
|
"row_length": 757,
|
|
"key_length": 757,
|
|
"unique_constraint": false,
|
|
"makes_grouped_rows": false,
|
|
"cannot_insert_duplicates": true,
|
|
"location": "memory (heap)",
|
|
"row_limit_estimate": 1
|
|
} /* tmp_table_info */
|
|
} /* creating_tmp_table */
|
|
},
|
|
{
|
|
"join_preparation": {
|
|
"select#": "fake",
|
|
"steps": [
|
|
{
|
|
"expanded_query": "/* select#fake */ select `c1` AS `c1`,`c2` AS `c2` from dual"
|
|
}
|
|
] /* steps */
|
|
} /* join_preparation */
|
|
},
|
|
{
|
|
"derived": {
|
|
"table": " `tt`",
|
|
"select#": 2,
|
|
"materialized": true
|
|
} /* derived */
|
|
},
|
|
{
|
|
"expanded_query": "/* select#1 */ select `tt`.`c1` AS `c1`,group_concat(`tt`.`c2` separator ',') AS `cc` from (/* select#2 */ select `t1`.`c1` AS `c1`,`t1`.`c2` AS `c2` from `t1` union /* select#3 */ select `t1`.`c1` AS `c1`,'a' AS `a` from `t1`) `tt` group by `tt`.`c1` order by `cc`"
|
|
}
|
|
] /* steps */
|
|
} /* join_preparation */
|
|
},
|
|
{
|
|
"join_optimization": {
|
|
"select#": 1,
|
|
"steps": [
|
|
{
|
|
"join_optimization": {
|
|
"select#": 2,
|
|
"steps": [
|
|
{
|
|
"table_dependencies": [
|
|
{
|
|
"table": "`t1`",
|
|
"row_may_be_null": false,
|
|
"map_bit": 0,
|
|
"depends_on_map_bits": [
|
|
] /* depends_on_map_bits */
|
|
}
|
|
] /* table_dependencies */
|
|
},
|
|
{
|
|
"rows_estimation": [
|
|
{
|
|
"table": "`t1`",
|
|
"table_scan": {
|
|
"rows": 66,
|
|
"cost": 0.25
|
|
} /* table_scan */
|
|
}
|
|
] /* rows_estimation */
|
|
},
|
|
{
|
|
"considered_execution_plans": [
|
|
{
|
|
"plan_prefix": [
|
|
] /* plan_prefix */,
|
|
"table": "`t1`",
|
|
"best_access_path": {
|
|
"considered_access_paths": [
|
|
{
|
|
"rows_to_scan": 66,
|
|
"access_type": "scan",
|
|
"resulting_rows": 66,
|
|
"cost": 6.85,
|
|
"chosen": true
|
|
}
|
|
] /* considered_access_paths */
|
|
} /* best_access_path */,
|
|
"condition_filtering_pct": 100,
|
|
"rows_for_plan": 66,
|
|
"cost_for_plan": 6.85,
|
|
"chosen": true
|
|
}
|
|
] /* considered_execution_plans */
|
|
},
|
|
{
|
|
"attaching_conditions_to_tables": {
|
|
"original_condition": null,
|
|
"attached_conditions_computation": [
|
|
] /* attached_conditions_computation */,
|
|
"attached_conditions_summary": [
|
|
{
|
|
"table": "`t1`",
|
|
"attached": null
|
|
}
|
|
] /* attached_conditions_summary */
|
|
} /* attaching_conditions_to_tables */
|
|
},
|
|
{
|
|
"finalizing_table_conditions": [
|
|
] /* finalizing_table_conditions */
|
|
},
|
|
{
|
|
"refine_plan": [
|
|
{
|
|
"table": "`t1`"
|
|
}
|
|
] /* refine_plan */
|
|
}
|
|
] /* steps */
|
|
} /* join_optimization */
|
|
},
|
|
{
|
|
"join_optimization": {
|
|
"select#": 3,
|
|
"steps": [
|
|
{
|
|
"table_dependencies": [
|
|
{
|
|
"table": "`t1`",
|
|
"row_may_be_null": false,
|
|
"map_bit": 0,
|
|
"depends_on_map_bits": [
|
|
] /* depends_on_map_bits */
|
|
}
|
|
] /* table_dependencies */
|
|
},
|
|
{
|
|
"rows_estimation": [
|
|
{
|
|
"table": "`t1`",
|
|
"table_scan": {
|
|
"rows": 66,
|
|
"cost": 0.25
|
|
} /* table_scan */
|
|
}
|
|
] /* rows_estimation */
|
|
},
|
|
{
|
|
"considered_execution_plans": [
|
|
{
|
|
"plan_prefix": [
|
|
] /* plan_prefix */,
|
|
"table": "`t1`",
|
|
"best_access_path": {
|
|
"considered_access_paths": [
|
|
{
|
|
"rows_to_scan": 66,
|
|
"access_type": "scan",
|
|
"resulting_rows": 66,
|
|
"cost": 6.85,
|
|
"chosen": true
|
|
}
|
|
] /* considered_access_paths */
|
|
} /* best_access_path */,
|
|
"condition_filtering_pct": 100,
|
|
"rows_for_plan": 66,
|
|
"cost_for_plan": 6.85,
|
|
"chosen": true
|
|
}
|
|
] /* considered_execution_plans */
|
|
},
|
|
{
|
|
"attaching_conditions_to_tables": {
|
|
"original_condition": null,
|
|
"attached_conditions_computation": [
|
|
] /* attached_conditions_computation */,
|
|
"attached_conditions_summary": [
|
|
{
|
|
"table": "`t1`",
|
|
"attached": null
|
|
}
|
|
] /* attached_conditions_summary */
|
|
} /* attaching_conditions_to_tables */
|
|
},
|
|
{
|
|
"finalizing_table_conditions": [
|
|
] /* finalizing_table_conditions */
|
|
},
|
|
{
|
|
"refine_plan": [
|
|
{
|
|
"table": "`t1`"
|
|
}
|
|
] /* refine_plan */
|
|
}
|
|
] /* steps */
|
|
} /* join_optimization */
|
|
},
|
|
{
|
|
"join_optimization": {
|
|
"select#": "fake",
|
|
"steps": [
|
|
{
|
|
"table_dependencies": [
|
|
{
|
|
"table": "``.``",
|
|
"row_may_be_null": false,
|
|
"map_bit": 0,
|
|
"depends_on_map_bits": [
|
|
] /* depends_on_map_bits */
|
|
}
|
|
] /* table_dependencies */
|
|
},
|
|
{
|
|
"rows_estimation": [
|
|
{
|
|
"table": "``.``",
|
|
"table_scan": {
|
|
"rows": 0,
|
|
"cost": 2.5
|
|
} /* table_scan */
|
|
}
|
|
] /* rows_estimation */
|
|
},
|
|
{
|
|
"considered_execution_plans": [
|
|
{
|
|
"plan_prefix": [
|
|
] /* plan_prefix */,
|
|
"table": "``.``",
|
|
"best_access_path": {
|
|
"considered_access_paths": [
|
|
{
|
|
"rows_to_scan": 0,
|
|
"access_type": "scan",
|
|
"resulting_rows": 0,
|
|
"cost": 2.5,
|
|
"chosen": true
|
|
}
|
|
] /* considered_access_paths */
|
|
} /* best_access_path */,
|
|
"condition_filtering_pct": 100,
|
|
"rows_for_plan": 0,
|
|
"cost_for_plan": 2.5,
|
|
"chosen": true
|
|
}
|
|
] /* considered_execution_plans */
|
|
},
|
|
{
|
|
"attaching_conditions_to_tables": {
|
|
"original_condition": null,
|
|
"attached_conditions_computation": [
|
|
] /* attached_conditions_computation */,
|
|
"attached_conditions_summary": [
|
|
{
|
|
"table": "``.``",
|
|
"attached": null
|
|
}
|
|
] /* attached_conditions_summary */
|
|
} /* attaching_conditions_to_tables */
|
|
},
|
|
{
|
|
"finalizing_table_conditions": [
|
|
] /* finalizing_table_conditions */
|
|
},
|
|
{
|
|
"refine_plan": [
|
|
{
|
|
"table": "``.``"
|
|
}
|
|
] /* refine_plan */
|
|
}
|
|
] /* steps */
|
|
} /* join_optimization */
|
|
},
|
|
{
|
|
"substitute_generated_columns": {
|
|
} /* substitute_generated_columns */
|
|
},
|
|
{
|
|
"table_dependencies": [
|
|
{
|
|
"table": " `tt`",
|
|
"row_may_be_null": false,
|
|
"map_bit": 0,
|
|
"depends_on_map_bits": [
|
|
] /* depends_on_map_bits */
|
|
}
|
|
] /* table_dependencies */
|
|
},
|
|
{
|
|
"rows_estimation": [
|
|
{
|
|
"table": " `tt`",
|
|
"table_scan": {
|
|
"rows": 132,
|
|
"cost": 4.15
|
|
} /* table_scan */
|
|
}
|
|
] /* rows_estimation */
|
|
},
|
|
{
|
|
"considered_execution_plans": [
|
|
{
|
|
"plan_prefix": [
|
|
] /* plan_prefix */,
|
|
"table": " `tt`",
|
|
"best_access_path": {
|
|
"considered_access_paths": [
|
|
{
|
|
"rows_to_scan": 132,
|
|
"access_type": "scan",
|
|
"resulting_rows": 132,
|
|
"cost": 17.35,
|
|
"chosen": true
|
|
}
|
|
] /* considered_access_paths */
|
|
} /* best_access_path */,
|
|
"condition_filtering_pct": 100,
|
|
"rows_for_plan": 132,
|
|
"cost_for_plan": 17.35,
|
|
"chosen": true
|
|
}
|
|
] /* considered_execution_plans */
|
|
},
|
|
{
|
|
"attaching_conditions_to_tables": {
|
|
"original_condition": null,
|
|
"attached_conditions_computation": [
|
|
] /* attached_conditions_computation */,
|
|
"attached_conditions_summary": [
|
|
{
|
|
"table": " `tt`",
|
|
"attached": null
|
|
}
|
|
] /* attached_conditions_summary */
|
|
} /* attaching_conditions_to_tables */
|
|
},
|
|
{
|
|
"optimizing_distinct_group_by_order_by": {
|
|
"simplifying_order_by": {
|
|
"original_clause": "`cc`",
|
|
"items": [
|
|
{
|
|
"item": "group_concat(`tt`.`c2` separator ',')"
|
|
}
|
|
] /* items */,
|
|
"resulting_clause_is_simple": false,
|
|
"resulting_clause": "`cc`"
|
|
} /* simplifying_order_by */,
|
|
"simplifying_group_by": {
|
|
"original_clause": "`tt`.`c1`",
|
|
"items": [
|
|
{
|
|
"item": "`tt`.`c1`"
|
|
}
|
|
] /* items */,
|
|
"resulting_clause_is_simple": true,
|
|
"resulting_clause": "`tt`.`c1`"
|
|
} /* simplifying_group_by */
|
|
} /* optimizing_distinct_group_by_order_by */
|
|
},
|
|
{
|
|
"finalizing_table_conditions": [
|
|
] /* finalizing_table_conditions */
|
|
},
|
|
{
|
|
"refine_plan": [
|
|
{
|
|
"table": " `tt`"
|
|
}
|
|
] /* refine_plan */
|
|
},
|
|
{
|
|
"considering_tmp_tables": [
|
|
{
|
|
"adding_tmp_table_in_plan_at_position": 1,
|
|
"filesort": {
|
|
"adding_sort_to_table_in_plan_at_position": 0
|
|
} /* filesort */,
|
|
"unknown_key_1": {
|
|
"creating_tmp_table": {
|
|
"tmp_table_info": {
|
|
"table": "intermediate_tmp_table",
|
|
"columns": 1,
|
|
"row_length": 753,
|
|
"key_length": 0,
|
|
"unique_constraint": false,
|
|
"makes_grouped_rows": false,
|
|
"cannot_insert_duplicates": false,
|
|
"location": "memory (heap)",
|
|
"row_limit_estimate": 1
|
|
} /* tmp_table_info */
|
|
} /* creating_tmp_table */
|
|
},
|
|
"write_method": "write_group_row_when_complete"
|
|
},
|
|
{
|
|
"adding_sort_to_table_in_plan_at_position": 1
|
|
} /* filesort */
|
|
] /* considering_tmp_tables */
|
|
}
|
|
] /* steps */
|
|
} /* join_optimization */
|
|
},
|
|
{
|
|
"join_execution": {
|
|
"select#": 1,
|
|
"steps": [
|
|
{
|
|
"creating_tmp_table": {
|
|
"tmp_table_info": {
|
|
"in_plan_at_position": 1,
|
|
"columns": 2,
|
|
"row_length": 1507,
|
|
"key_length": 0,
|
|
"unique_constraint": false,
|
|
"makes_grouped_rows": false,
|
|
"cannot_insert_duplicates": false,
|
|
"location": "memory (heap)",
|
|
"row_limit_estimate": 1
|
|
} /* tmp_table_info */
|
|
} /* creating_tmp_table */
|
|
},
|
|
{
|
|
"materialize": {
|
|
"select#": 1,
|
|
"steps": [
|
|
{
|
|
"creating_tmp_table": {
|
|
"tmp_table_info": {
|
|
"table": " `tt`",
|
|
"in_plan_at_position": 0,
|
|
"columns": 2,
|
|
"row_length": 757,
|
|
"key_length": 757,
|
|
"unique_constraint": false,
|
|
"makes_grouped_rows": false,
|
|
"cannot_insert_duplicates": true,
|
|
"location": "memory (heap)",
|
|
"row_limit_estimate": 1
|
|
} /* tmp_table_info */
|
|
} /* creating_tmp_table */
|
|
},
|
|
{
|
|
"materialize": {
|
|
"select#": 2,
|
|
"steps": [
|
|
{
|
|
"converting_tmp_table_to_ondisk": {
|
|
"cause": "memory_table_size_exceeded",
|
|
"tmp_table_info": {
|
|
"table": " `tt`",
|
|
"in_plan_at_position": 0,
|
|
"columns": 2,
|
|
"row_length": 757,
|
|
"key_length": 757,
|
|
"unique_constraint": false,
|
|
"makes_grouped_rows": false,
|
|
"cannot_insert_duplicates": true,
|
|
"location": "disk (TMP_TABLE_ENGINE)",
|
|
"record_format": "packed"
|
|
} /* tmp_table_info */
|
|
} /* converting_tmp_table_to_ondisk */
|
|
}
|
|
] /* steps */
|
|
} /* materialize */
|
|
},
|
|
{
|
|
"materialize": {
|
|
"select#": 3,
|
|
"steps": [
|
|
] /* steps */
|
|
} /* materialize */
|
|
},
|
|
{
|
|
"sorting_table_in_plan_at_position": 0,
|
|
"filesort_information": [
|
|
{
|
|
"direction": "asc",
|
|
"table": " `tt`",
|
|
"field": "c1"
|
|
}
|
|
] /* filesort_information */,
|
|
"filesort_priority_queue_optimization": {
|
|
"usable": false,
|
|
"cause": "not applicable (no LIMIT)"
|
|
} /* filesort_priority_queue_optimization */,
|
|
"filesort_execution": [
|
|
] /* filesort_execution */,
|
|
"filesort_summary": {
|
|
"memory_available": 262144,
|
|
"key_size": 4,
|
|
"row_size": 763,
|
|
"max_rows_per_buffer": 340,
|
|
"num_rows_estimate": 1820,
|
|
"num_rows_found": 132,
|
|
"num_initial_chunks_spilled_to_disk": 0,
|
|
"peak_memory_used": "NNN",
|
|
"sort_algorithm": "std::stable_sort",
|
|
"sort_mode": "<fixed_sort_key, packed_additional_fields>"
|
|
} /* filesort_summary */
|
|
},
|
|
{
|
|
"converting_tmp_table_to_ondisk": {
|
|
"cause": "memory_table_size_exceeded",
|
|
"tmp_table_info": {
|
|
"in_plan_at_position": 1,
|
|
"columns": 2,
|
|
"row_length": 1507,
|
|
"key_length": 0,
|
|
"unique_constraint": false,
|
|
"makes_grouped_rows": false,
|
|
"cannot_insert_duplicates": false,
|
|
"location": "disk (TMP_TABLE_ENGINE)",
|
|
"record_format": "packed"
|
|
} /* tmp_table_info */
|
|
} /* converting_tmp_table_to_ondisk */
|
|
}
|
|
] /* steps */
|
|
} /* materialize */
|
|
},
|
|
{
|
|
"sorting_table_in_plan_at_position": 1,
|
|
"filesort_information": [
|
|
{
|
|
"direction": "asc",
|
|
"field": "cc"
|
|
}
|
|
] /* filesort_information */,
|
|
"filesort_priority_queue_optimization": {
|
|
"usable": false,
|
|
"cause": "not applicable (no LIMIT)"
|
|
} /* filesort_priority_queue_optimization */,
|
|
"filesort_execution": [
|
|
] /* filesort_execution */,
|
|
"filesort_summary": {
|
|
"memory_available": 262144,
|
|
"key_size": 1007,
|
|
"row_size": 1007,
|
|
"max_rows_per_buffer": 258,
|
|
"num_rows_estimate": 1365,
|
|
"num_rows_found": 66,
|
|
"num_initial_chunks_spilled_to_disk": 0,
|
|
"peak_memory_used": "NNN",
|
|
"sort_algorithm": "std::sort",
|
|
"unpacked_addon_fields": "max_length_for_sort_data",
|
|
"sort_mode": "<fixed_sort_key, rowid>"
|
|
} /* filesort_summary */
|
|
}
|
|
] /* steps */
|
|
} /* join_execution */
|
|
}
|
|
] /* steps */
|
|
} 0 0
|
|
DROP TABLE t1;
|
|
#
|
|
SET SESSION internal_tmp_mem_storage_engine=default;
|
|
SET max_length_for_sort_data=DEFAULT;
|