1011 lines
27 KiB
Plaintext
1011 lines
27 KiB
Plaintext
CREATE TABLE t0 (
|
|
i1 INTEGER
|
|
);
|
|
INSERT INTO t0 VALUE (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
|
|
CREATE TABLE t1 (
|
|
pk INTEGER PRIMARY KEY,
|
|
i1 INTEGER NOT NULL,
|
|
c1 CHAR(250),
|
|
c2 CHAR(250),
|
|
c3 CHAR(250),
|
|
c4 CHAR(250),
|
|
INDEX i1_key (i1)
|
|
) ENGINE=XENGINE;
|
|
INSERT INTO t1
|
|
SELECT a0.i1 + 10 * a1.i1, a0.i1, 'abc', 'def', 'ghi', 'jkl'
|
|
FROM t0 AS a0, t0 AS a1;
|
|
ANALYZE TABLE t1;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status OK
|
|
EXPLAIN FORMAT=JSON SELECT * FROM t1;
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"cost_info": {
|
|
"query_cost": "13.75"
|
|
},
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"rows_examined_per_scan": 100,
|
|
"rows_produced_per_join": 100,
|
|
"filtered": "100.00",
|
|
"cost_info": {
|
|
"read_cost": "3.75",
|
|
"eval_cost": "10.00",
|
|
"prefix_cost": "13.75",
|
|
"data_read_per_join": "392K"
|
|
},
|
|
"used_columns": [
|
|
"pk",
|
|
"i1",
|
|
"c1",
|
|
"c2",
|
|
"c3",
|
|
"c4"
|
|
]
|
|
}
|
|
}
|
|
}
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`pk` AS `pk`,`test`.`t1`.`i1` AS `i1`,`test`.`t1`.`c1` AS `c1`,`test`.`t1`.`c2` AS `c2`,`test`.`t1`.`c3` AS `c3`,`test`.`t1`.`c4` AS `c4` from `test`.`t1`
|
|
#
|
|
# Test that updating the cost constants does not influence on an
|
|
# existing connection
|
|
#
|
|
UPDATE mysql.server_cost
|
|
SET cost_value=0.4
|
|
WHERE cost_name="row_evaluate_cost";
|
|
FLUSH OPTIMIZER_COSTS;
|
|
#
|
|
# The cost estimates should not have changed
|
|
#
|
|
EXPLAIN FORMAT=JSON SELECT * FROM t1;
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"cost_info": {
|
|
"query_cost": "13.75"
|
|
},
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"rows_examined_per_scan": 100,
|
|
"rows_produced_per_join": 100,
|
|
"filtered": "100.00",
|
|
"cost_info": {
|
|
"read_cost": "3.75",
|
|
"eval_cost": "10.00",
|
|
"prefix_cost": "13.75",
|
|
"data_read_per_join": "392K"
|
|
},
|
|
"used_columns": [
|
|
"pk",
|
|
"i1",
|
|
"c1",
|
|
"c2",
|
|
"c3",
|
|
"c4"
|
|
]
|
|
}
|
|
}
|
|
}
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`pk` AS `pk`,`test`.`t1`.`i1` AS `i1`,`test`.`t1`.`c1` AS `c1`,`test`.`t1`.`c2` AS `c2`,`test`.`t1`.`c3` AS `c3`,`test`.`t1`.`c4` AS `c4` from `test`.`t1`
|
|
UPDATE mysql.server_cost
|
|
SET cost_value=DEFAULT;
|
|
#
|
|
# Test effect of changing the row_evalute_cost
|
|
#
|
|
UPDATE mysql.server_cost
|
|
SET cost_value=0.2
|
|
WHERE cost_name="row_evaluate_cost";
|
|
FLUSH OPTIMIZER_COSTS;
|
|
#
|
|
# Verify that the cost estimate for evalution cost has doubled
|
|
#
|
|
EXPLAIN FORMAT=JSON SELECT * FROM t1;
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"cost_info": {
|
|
"query_cost": "23.75"
|
|
},
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"rows_examined_per_scan": 100,
|
|
"rows_produced_per_join": 100,
|
|
"filtered": "100.00",
|
|
"cost_info": {
|
|
"read_cost": "3.75",
|
|
"eval_cost": "20.00",
|
|
"prefix_cost": "23.75",
|
|
"data_read_per_join": "392K"
|
|
},
|
|
"used_columns": [
|
|
"pk",
|
|
"i1",
|
|
"c1",
|
|
"c2",
|
|
"c3",
|
|
"c4"
|
|
]
|
|
}
|
|
}
|
|
}
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`pk` AS `pk`,`test`.`t1`.`i1` AS `i1`,`test`.`t1`.`c1` AS `c1`,`test`.`t1`.`c2` AS `c2`,`test`.`t1`.`c3` AS `c3`,`test`.`t1`.`c4` AS `c4` from `test`.`t1`
|
|
UPDATE mysql.server_cost
|
|
SET cost_value=DEFAULT;
|
|
#
|
|
# Test effect of changing key_compare_cost
|
|
#
|
|
# Use a query that will use loose index scan since loose index scan
|
|
# is comparing keys in order to determine group boundaries.
|
|
# Result from running query with default cost constants:
|
|
#
|
|
EXPLAIN FORMAT=JSON SELECT DISTINCT(i1) FROM t1;
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"cost_info": {
|
|
"query_cost": "13.00"
|
|
},
|
|
"duplicates_removal": {
|
|
"using_filesort": false,
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "range",
|
|
"possible_keys": [
|
|
"i1_key"
|
|
],
|
|
"key": "i1_key",
|
|
"used_key_parts": [
|
|
"i1"
|
|
],
|
|
"key_length": "4",
|
|
"rows_examined_per_scan": 51,
|
|
"rows_produced_per_join": 51,
|
|
"filtered": "100.00",
|
|
"using_index_for_group_by": true,
|
|
"cost_info": {
|
|
"read_cost": "7.90",
|
|
"eval_cost": "5.10",
|
|
"prefix_cost": "13.00",
|
|
"data_read_per_join": "200K"
|
|
},
|
|
"used_columns": [
|
|
"pk",
|
|
"i1"
|
|
]
|
|
}
|
|
}
|
|
}
|
|
}
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select distinct `test`.`t1`.`i1` AS `i1` from `test`.`t1`
|
|
UPDATE mysql.server_cost
|
|
SET cost_value=0.1
|
|
WHERE cost_name="key_compare_cost";
|
|
FLUSH OPTIMIZER_COSTS;
|
|
#
|
|
# Verify that the cost estimate for read cost is increased
|
|
#
|
|
EXPLAIN FORMAT=JSON SELECT DISTINCT(i1) FROM t1;
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"cost_info": {
|
|
"query_cost": "13.75"
|
|
},
|
|
"duplicates_removal": {
|
|
"using_filesort": false,
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "index",
|
|
"possible_keys": [
|
|
"i1_key"
|
|
],
|
|
"key": "i1_key",
|
|
"used_key_parts": [
|
|
"i1"
|
|
],
|
|
"key_length": "4",
|
|
"rows_examined_per_scan": 100,
|
|
"rows_produced_per_join": 100,
|
|
"filtered": "100.00",
|
|
"using_index": true,
|
|
"cost_info": {
|
|
"read_cost": "3.75",
|
|
"eval_cost": "10.00",
|
|
"prefix_cost": "13.75",
|
|
"data_read_per_join": "392K"
|
|
},
|
|
"used_columns": [
|
|
"pk",
|
|
"i1"
|
|
]
|
|
}
|
|
}
|
|
}
|
|
}
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select distinct `test`.`t1`.`i1` AS `i1` from `test`.`t1`
|
|
UPDATE mysql.server_cost
|
|
SET cost_value=DEFAULT;
|
|
#
|
|
# Test effect of changing memory_temptable_create_cost
|
|
#
|
|
# Use a query that creates a temporary table for a subquery.
|
|
# Run query with default cost constants.
|
|
#
|
|
EXPLAIN FORMAT=JSON SELECT * FROM t1
|
|
WHERE i1 IN (SELECT i1 FROM t1);
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"cost_info": {
|
|
"query_cost": "48.50"
|
|
},
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"possible_keys": [
|
|
"i1_key"
|
|
],
|
|
"rows_examined_per_scan": 100,
|
|
"rows_produced_per_join": 100,
|
|
"filtered": "100.00",
|
|
"cost_info": {
|
|
"read_cost": "3.75",
|
|
"eval_cost": "10.00",
|
|
"prefix_cost": "13.75",
|
|
"data_read_per_join": "392K"
|
|
},
|
|
"used_columns": [
|
|
"pk",
|
|
"i1",
|
|
"c1",
|
|
"c2",
|
|
"c3",
|
|
"c4"
|
|
],
|
|
"attached_condition": "(`test`.`t1`.`i1` is not null)"
|
|
}
|
|
},
|
|
{
|
|
"table": {
|
|
"table_name": "<subquery2>",
|
|
"access_type": "eq_ref",
|
|
"key": "<auto_distinct_key>",
|
|
"key_length": "4",
|
|
"ref": [
|
|
"test.t1.i1"
|
|
],
|
|
"rows_examined_per_scan": 1,
|
|
"materialized_from_subquery": {
|
|
"using_temporary_table": true,
|
|
"query_block": {
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "index",
|
|
"possible_keys": [
|
|
"i1_key"
|
|
],
|
|
"key": "i1_key",
|
|
"used_key_parts": [
|
|
"i1"
|
|
],
|
|
"key_length": "4",
|
|
"rows_examined_per_scan": 100,
|
|
"rows_produced_per_join": 100,
|
|
"filtered": "100.00",
|
|
"using_index": true,
|
|
"cost_info": {
|
|
"read_cost": "3.75",
|
|
"eval_cost": "10.00",
|
|
"prefix_cost": "13.75",
|
|
"data_read_per_join": "392K"
|
|
},
|
|
"used_columns": [
|
|
"i1"
|
|
]
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
]
|
|
}
|
|
}
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`pk` AS `pk`,`test`.`t1`.`i1` AS `i1`,`test`.`t1`.`c1` AS `c1`,`test`.`t1`.`c2` AS `c2`,`test`.`t1`.`c3` AS `c3`,`test`.`t1`.`c4` AS `c4` from `test`.`t1` semi join (`test`.`t1`) where (`<subquery2>`.`i1` = `test`.`t1`.`i1`)
|
|
UPDATE mysql.server_cost
|
|
SET cost_value=10.0
|
|
WHERE cost_name="memory_temptable_create_cost";
|
|
FLUSH OPTIMIZER_COSTS;
|
|
#
|
|
# Verify that the cost estimate for the query is incrased (by 9.0)
|
|
#
|
|
EXPLAIN FORMAT=JSON SELECT * FROM t1
|
|
WHERE i1 IN (SELECT i1 FROM t1);
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"cost_info": {
|
|
"query_cost": "57.50"
|
|
},
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"possible_keys": [
|
|
"i1_key"
|
|
],
|
|
"rows_examined_per_scan": 100,
|
|
"rows_produced_per_join": 100,
|
|
"filtered": "100.00",
|
|
"cost_info": {
|
|
"read_cost": "3.75",
|
|
"eval_cost": "10.00",
|
|
"prefix_cost": "13.75",
|
|
"data_read_per_join": "392K"
|
|
},
|
|
"used_columns": [
|
|
"pk",
|
|
"i1",
|
|
"c1",
|
|
"c2",
|
|
"c3",
|
|
"c4"
|
|
],
|
|
"attached_condition": "(`test`.`t1`.`i1` is not null)"
|
|
}
|
|
},
|
|
{
|
|
"table": {
|
|
"table_name": "<subquery2>",
|
|
"access_type": "eq_ref",
|
|
"key": "<auto_distinct_key>",
|
|
"key_length": "4",
|
|
"ref": [
|
|
"test.t1.i1"
|
|
],
|
|
"rows_examined_per_scan": 1,
|
|
"materialized_from_subquery": {
|
|
"using_temporary_table": true,
|
|
"query_block": {
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "index",
|
|
"possible_keys": [
|
|
"i1_key"
|
|
],
|
|
"key": "i1_key",
|
|
"used_key_parts": [
|
|
"i1"
|
|
],
|
|
"key_length": "4",
|
|
"rows_examined_per_scan": 100,
|
|
"rows_produced_per_join": 100,
|
|
"filtered": "100.00",
|
|
"using_index": true,
|
|
"cost_info": {
|
|
"read_cost": "3.75",
|
|
"eval_cost": "10.00",
|
|
"prefix_cost": "13.75",
|
|
"data_read_per_join": "392K"
|
|
},
|
|
"used_columns": [
|
|
"i1"
|
|
]
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
]
|
|
}
|
|
}
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`pk` AS `pk`,`test`.`t1`.`i1` AS `i1`,`test`.`t1`.`c1` AS `c1`,`test`.`t1`.`c2` AS `c2`,`test`.`t1`.`c3` AS `c3`,`test`.`t1`.`c4` AS `c4` from `test`.`t1` semi join (`test`.`t1`) where (`<subquery2>`.`i1` = `test`.`t1`.`i1`)
|
|
UPDATE mysql.server_cost
|
|
SET cost_value=DEFAULT;
|
|
#
|
|
# Test effect of changing memory_temptable_row_cost
|
|
#
|
|
# Use a query that creates a temporary table for a subquery.
|
|
# Result from running query with default cost constants:
|
|
#
|
|
EXPLAIN FORMAT=JSON SELECT * FROM t1
|
|
WHERE i1 IN (SELECT i1 FROM t1);
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"cost_info": {
|
|
"query_cost": "48.50"
|
|
},
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"possible_keys": [
|
|
"i1_key"
|
|
],
|
|
"rows_examined_per_scan": 100,
|
|
"rows_produced_per_join": 100,
|
|
"filtered": "100.00",
|
|
"cost_info": {
|
|
"read_cost": "3.75",
|
|
"eval_cost": "10.00",
|
|
"prefix_cost": "13.75",
|
|
"data_read_per_join": "392K"
|
|
},
|
|
"used_columns": [
|
|
"pk",
|
|
"i1",
|
|
"c1",
|
|
"c2",
|
|
"c3",
|
|
"c4"
|
|
],
|
|
"attached_condition": "(`test`.`t1`.`i1` is not null)"
|
|
}
|
|
},
|
|
{
|
|
"table": {
|
|
"table_name": "<subquery2>",
|
|
"access_type": "eq_ref",
|
|
"key": "<auto_distinct_key>",
|
|
"key_length": "4",
|
|
"ref": [
|
|
"test.t1.i1"
|
|
],
|
|
"rows_examined_per_scan": 1,
|
|
"materialized_from_subquery": {
|
|
"using_temporary_table": true,
|
|
"query_block": {
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "index",
|
|
"possible_keys": [
|
|
"i1_key"
|
|
],
|
|
"key": "i1_key",
|
|
"used_key_parts": [
|
|
"i1"
|
|
],
|
|
"key_length": "4",
|
|
"rows_examined_per_scan": 100,
|
|
"rows_produced_per_join": 100,
|
|
"filtered": "100.00",
|
|
"using_index": true,
|
|
"cost_info": {
|
|
"read_cost": "3.75",
|
|
"eval_cost": "10.00",
|
|
"prefix_cost": "13.75",
|
|
"data_read_per_join": "392K"
|
|
},
|
|
"used_columns": [
|
|
"i1"
|
|
]
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
]
|
|
}
|
|
}
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`pk` AS `pk`,`test`.`t1`.`i1` AS `i1`,`test`.`t1`.`c1` AS `c1`,`test`.`t1`.`c2` AS `c2`,`test`.`t1`.`c3` AS `c3`,`test`.`t1`.`c4` AS `c4` from `test`.`t1` semi join (`test`.`t1`) where (`<subquery2>`.`i1` = `test`.`t1`.`i1`)
|
|
UPDATE mysql.server_cost
|
|
SET cost_value=0.2
|
|
WHERE cost_name="memory_temptable_row_cost";
|
|
FLUSH OPTIMIZER_COSTS;
|
|
#
|
|
# Verify that the cost estimate for the query is incrased by:
|
|
# 100 rows that needs to be inserted and read from the temporary table.
|
|
# The cost of each insert and read is increased by 0.1, ie cost should
|
|
# be increased by 100 * 2 * 0.1 = 20.
|
|
#
|
|
EXPLAIN FORMAT=JSON SELECT * FROM t1
|
|
WHERE i1 IN (SELECT i1 FROM t1);
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"cost_info": {
|
|
"query_cost": "58.79"
|
|
},
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"possible_keys": [
|
|
"i1_key"
|
|
],
|
|
"rows_examined_per_scan": 100,
|
|
"rows_produced_per_join": 100,
|
|
"filtered": "100.00",
|
|
"cost_info": {
|
|
"read_cost": "3.75",
|
|
"eval_cost": "10.00",
|
|
"prefix_cost": "13.75",
|
|
"data_read_per_join": "392K"
|
|
},
|
|
"used_columns": [
|
|
"pk",
|
|
"i1",
|
|
"c1",
|
|
"c2",
|
|
"c3",
|
|
"c4"
|
|
]
|
|
}
|
|
},
|
|
{
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ref",
|
|
"possible_keys": [
|
|
"i1_key"
|
|
],
|
|
"key": "i1_key",
|
|
"used_key_parts": [
|
|
"i1"
|
|
],
|
|
"key_length": "4",
|
|
"ref": [
|
|
"test.t1.i1"
|
|
],
|
|
"rows_examined_per_scan": 2,
|
|
"rows_produced_per_join": 100,
|
|
"filtered": "100.00",
|
|
"using_index": true,
|
|
"first_match": "t1",
|
|
"cost_info": {
|
|
"read_cost": "25.04",
|
|
"eval_cost": "10.00",
|
|
"prefix_cost": "58.79",
|
|
"data_read_per_join": "392K"
|
|
},
|
|
"used_columns": [
|
|
"i1"
|
|
]
|
|
}
|
|
}
|
|
]
|
|
}
|
|
}
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`pk` AS `pk`,`test`.`t1`.`i1` AS `i1`,`test`.`t1`.`c1` AS `c1`,`test`.`t1`.`c2` AS `c2`,`test`.`t1`.`c3` AS `c3`,`test`.`t1`.`c4` AS `c4` from `test`.`t1` semi join (`test`.`t1`) where (`test`.`t1`.`i1` = `test`.`t1`.`i1`)
|
|
UPDATE mysql.server_cost
|
|
SET cost_value=DEFAULT;
|
|
set global internal_tmp_mem_storage_engine='memory';
|
|
set session internal_tmp_mem_storage_engine='memory';
|
|
set @max_heap_table_size_save= @@max_heap_table_size;
|
|
set max_heap_table_size= 16384;
|
|
#
|
|
# Test effect of changing disk_temptable_create_cost
|
|
#
|
|
# Use a query that creates a temporary table for a subquery.
|
|
# Result from running query with default cost constants:
|
|
#
|
|
EXPLAIN FORMAT=JSON SELECT * FROM t1
|
|
WHERE c1 IN (SELECT c1 FROM t1);
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"cost_info": {
|
|
"query_cost": "147.50"
|
|
},
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"rows_examined_per_scan": 100,
|
|
"rows_produced_per_join": 100,
|
|
"filtered": "100.00",
|
|
"cost_info": {
|
|
"read_cost": "3.75",
|
|
"eval_cost": "10.00",
|
|
"prefix_cost": "13.75",
|
|
"data_read_per_join": "392K"
|
|
},
|
|
"used_columns": [
|
|
"pk",
|
|
"i1",
|
|
"c1",
|
|
"c2",
|
|
"c3",
|
|
"c4"
|
|
],
|
|
"attached_condition": "(`test`.`t1`.`c1` is not null)"
|
|
}
|
|
},
|
|
{
|
|
"table": {
|
|
"table_name": "<subquery2>",
|
|
"access_type": "eq_ref",
|
|
"key": "<auto_distinct_key>",
|
|
"key_length": "1001",
|
|
"ref": [
|
|
"test.t1.c1"
|
|
],
|
|
"rows_examined_per_scan": 1,
|
|
"materialized_from_subquery": {
|
|
"using_temporary_table": true,
|
|
"query_block": {
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"rows_examined_per_scan": 100,
|
|
"rows_produced_per_join": 100,
|
|
"filtered": "100.00",
|
|
"cost_info": {
|
|
"read_cost": "3.75",
|
|
"eval_cost": "10.00",
|
|
"prefix_cost": "13.75",
|
|
"data_read_per_join": "392K"
|
|
},
|
|
"used_columns": [
|
|
"c1"
|
|
]
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
]
|
|
}
|
|
}
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`pk` AS `pk`,`test`.`t1`.`i1` AS `i1`,`test`.`t1`.`c1` AS `c1`,`test`.`t1`.`c2` AS `c2`,`test`.`t1`.`c3` AS `c3`,`test`.`t1`.`c4` AS `c4` from `test`.`t1` semi join (`test`.`t1`) where (`<subquery2>`.`c1` = `test`.`t1`.`c1`)
|
|
UPDATE mysql.server_cost
|
|
SET cost_value=40.0
|
|
WHERE cost_name="disk_temptable_create_cost";
|
|
FLUSH OPTIMIZER_COSTS;
|
|
set max_heap_table_size= 16384;
|
|
#
|
|
# Verify that the cost estimate for the query is incrased (by 20.0)
|
|
#
|
|
EXPLAIN FORMAT=JSON SELECT * FROM t1
|
|
WHERE c1 IN (SELECT c1 FROM t1);
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"cost_info": {
|
|
"query_cost": "167.50"
|
|
},
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"rows_examined_per_scan": 100,
|
|
"rows_produced_per_join": 100,
|
|
"filtered": "100.00",
|
|
"cost_info": {
|
|
"read_cost": "3.75",
|
|
"eval_cost": "10.00",
|
|
"prefix_cost": "13.75",
|
|
"data_read_per_join": "392K"
|
|
},
|
|
"used_columns": [
|
|
"pk",
|
|
"i1",
|
|
"c1",
|
|
"c2",
|
|
"c3",
|
|
"c4"
|
|
],
|
|
"attached_condition": "(`test`.`t1`.`c1` is not null)"
|
|
}
|
|
},
|
|
{
|
|
"table": {
|
|
"table_name": "<subquery2>",
|
|
"access_type": "eq_ref",
|
|
"key": "<auto_distinct_key>",
|
|
"key_length": "1001",
|
|
"ref": [
|
|
"test.t1.c1"
|
|
],
|
|
"rows_examined_per_scan": 1,
|
|
"materialized_from_subquery": {
|
|
"using_temporary_table": true,
|
|
"query_block": {
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"rows_examined_per_scan": 100,
|
|
"rows_produced_per_join": 100,
|
|
"filtered": "100.00",
|
|
"cost_info": {
|
|
"read_cost": "3.75",
|
|
"eval_cost": "10.00",
|
|
"prefix_cost": "13.75",
|
|
"data_read_per_join": "392K"
|
|
},
|
|
"used_columns": [
|
|
"c1"
|
|
]
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
]
|
|
}
|
|
}
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`pk` AS `pk`,`test`.`t1`.`i1` AS `i1`,`test`.`t1`.`c1` AS `c1`,`test`.`t1`.`c2` AS `c2`,`test`.`t1`.`c3` AS `c3`,`test`.`t1`.`c4` AS `c4` from `test`.`t1` semi join (`test`.`t1`) where (`<subquery2>`.`c1` = `test`.`t1`.`c1`)
|
|
UPDATE mysql.server_cost
|
|
SET cost_value=DEFAULT;
|
|
#
|
|
# Test effect of changing disk_temptable_row_cost
|
|
#
|
|
# Use a query that creates a temporary table for a subquery
|
|
# Result from running query with default cost constants:
|
|
#
|
|
EXPLAIN FORMAT=JSON SELECT * FROM t1
|
|
WHERE c1 IN (SELECT c1 FROM t1);
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"cost_info": {
|
|
"query_cost": "147.50"
|
|
},
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"rows_examined_per_scan": 100,
|
|
"rows_produced_per_join": 100,
|
|
"filtered": "100.00",
|
|
"cost_info": {
|
|
"read_cost": "3.75",
|
|
"eval_cost": "10.00",
|
|
"prefix_cost": "13.75",
|
|
"data_read_per_join": "392K"
|
|
},
|
|
"used_columns": [
|
|
"pk",
|
|
"i1",
|
|
"c1",
|
|
"c2",
|
|
"c3",
|
|
"c4"
|
|
],
|
|
"attached_condition": "(`test`.`t1`.`c1` is not null)"
|
|
}
|
|
},
|
|
{
|
|
"table": {
|
|
"table_name": "<subquery2>",
|
|
"access_type": "eq_ref",
|
|
"key": "<auto_distinct_key>",
|
|
"key_length": "1001",
|
|
"ref": [
|
|
"test.t1.c1"
|
|
],
|
|
"rows_examined_per_scan": 1,
|
|
"materialized_from_subquery": {
|
|
"using_temporary_table": true,
|
|
"query_block": {
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"rows_examined_per_scan": 100,
|
|
"rows_produced_per_join": 100,
|
|
"filtered": "100.00",
|
|
"cost_info": {
|
|
"read_cost": "3.75",
|
|
"eval_cost": "10.00",
|
|
"prefix_cost": "13.75",
|
|
"data_read_per_join": "392K"
|
|
},
|
|
"used_columns": [
|
|
"c1"
|
|
]
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
]
|
|
}
|
|
}
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`pk` AS `pk`,`test`.`t1`.`i1` AS `i1`,`test`.`t1`.`c1` AS `c1`,`test`.`t1`.`c2` AS `c2`,`test`.`t1`.`c3` AS `c3`,`test`.`t1`.`c4` AS `c4` from `test`.`t1` semi join (`test`.`t1`) where (`<subquery2>`.`c1` = `test`.`t1`.`c1`)
|
|
UPDATE mysql.server_cost
|
|
SET cost_value=1.0
|
|
WHERE cost_name="disk_temptable_row_cost";
|
|
FLUSH OPTIMIZER_COSTS;
|
|
set max_heap_table_size= 16384;
|
|
#
|
|
# Verify that the cost estimate for the query is incrased by:
|
|
# 100 rows that needs to be inserted and read from the temporary table.
|
|
# The cost of each insert and read is increased by 0.5, ie cost should
|
|
# be increased by 100 * 2 * 0.5 = 100.
|
|
#
|
|
EXPLAIN FORMAT=JSON SELECT * FROM t1
|
|
WHERE c1 IN (SELECT c1 FROM t1);
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"cost_info": {
|
|
"query_cost": "247.50"
|
|
},
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"rows_examined_per_scan": 100,
|
|
"rows_produced_per_join": 100,
|
|
"filtered": "100.00",
|
|
"cost_info": {
|
|
"read_cost": "3.75",
|
|
"eval_cost": "10.00",
|
|
"prefix_cost": "13.75",
|
|
"data_read_per_join": "392K"
|
|
},
|
|
"used_columns": [
|
|
"pk",
|
|
"i1",
|
|
"c1",
|
|
"c2",
|
|
"c3",
|
|
"c4"
|
|
],
|
|
"attached_condition": "(`test`.`t1`.`c1` is not null)"
|
|
}
|
|
},
|
|
{
|
|
"table": {
|
|
"table_name": "<subquery2>",
|
|
"access_type": "eq_ref",
|
|
"key": "<auto_distinct_key>",
|
|
"key_length": "1001",
|
|
"ref": [
|
|
"test.t1.c1"
|
|
],
|
|
"rows_examined_per_scan": 1,
|
|
"materialized_from_subquery": {
|
|
"using_temporary_table": true,
|
|
"query_block": {
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"rows_examined_per_scan": 100,
|
|
"rows_produced_per_join": 100,
|
|
"filtered": "100.00",
|
|
"cost_info": {
|
|
"read_cost": "3.75",
|
|
"eval_cost": "10.00",
|
|
"prefix_cost": "13.75",
|
|
"data_read_per_join": "392K"
|
|
},
|
|
"used_columns": [
|
|
"c1"
|
|
]
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
]
|
|
}
|
|
}
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`pk` AS `pk`,`test`.`t1`.`i1` AS `i1`,`test`.`t1`.`c1` AS `c1`,`test`.`t1`.`c2` AS `c2`,`test`.`t1`.`c3` AS `c3`,`test`.`t1`.`c4` AS `c4` from `test`.`t1` semi join (`test`.`t1`) where (`<subquery2>`.`c1` = `test`.`t1`.`c1`)
|
|
UPDATE mysql.server_cost
|
|
SET cost_value=DEFAULT;
|
|
set max_heap_table_size= @max_heap_table_size_save;
|
|
set session internal_tmp_mem_storage_engine=default;
|
|
set global internal_tmp_mem_storage_engine=default;
|
|
#
|
|
# Test effect of changing the memory_block_read_cost
|
|
#
|
|
# Result from running query with default cost constants:
|
|
#
|
|
EXPLAIN FORMAT=JSON SELECT * FROM t1;
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"cost_info": {
|
|
"query_cost": "13.75"
|
|
},
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"rows_examined_per_scan": 100,
|
|
"rows_produced_per_join": 100,
|
|
"filtered": "100.00",
|
|
"cost_info": {
|
|
"read_cost": "3.75",
|
|
"eval_cost": "10.00",
|
|
"prefix_cost": "13.75",
|
|
"data_read_per_join": "392K"
|
|
},
|
|
"used_columns": [
|
|
"pk",
|
|
"i1",
|
|
"c1",
|
|
"c2",
|
|
"c3",
|
|
"c4"
|
|
]
|
|
}
|
|
}
|
|
}
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`pk` AS `pk`,`test`.`t1`.`i1` AS `i1`,`test`.`t1`.`c1` AS `c1`,`test`.`t1`.`c2` AS `c2`,`test`.`t1`.`c3` AS `c3`,`test`.`t1`.`c4` AS `c4` from `test`.`t1`
|
|
UPDATE mysql.engine_cost
|
|
SET cost_value=0.5
|
|
WHERE cost_name="memory_block_read_cost";
|
|
FLUSH OPTIMIZER_COSTS;
|
|
#
|
|
# Verify that the cost estimate for read cost has doubled
|
|
#
|
|
EXPLAIN FORMAT=JSON SELECT * FROM t1;
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"cost_info": {
|
|
"query_cost": "17.50"
|
|
},
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"rows_examined_per_scan": 100,
|
|
"rows_produced_per_join": 100,
|
|
"filtered": "100.00",
|
|
"cost_info": {
|
|
"read_cost": "7.50",
|
|
"eval_cost": "10.00",
|
|
"prefix_cost": "17.50",
|
|
"data_read_per_join": "392K"
|
|
},
|
|
"used_columns": [
|
|
"pk",
|
|
"i1",
|
|
"c1",
|
|
"c2",
|
|
"c3",
|
|
"c4"
|
|
]
|
|
}
|
|
}
|
|
}
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`pk` AS `pk`,`test`.`t1`.`i1` AS `i1`,`test`.`t1`.`c1` AS `c1`,`test`.`t1`.`c2` AS `c2`,`test`.`t1`.`c3` AS `c3`,`test`.`t1`.`c4` AS `c4` from `test`.`t1`
|
|
UPDATE mysql.engine_cost
|
|
SET cost_value=DEFAULT;
|
|
FLUSH OPTIMIZER_COSTS;
|
|
DROP TABLE t0,t1;
|