163 lines
6.6 KiB
Plaintext
163 lines
6.6 KiB
Plaintext
SELECT * FROM mysql.server_cost;
|
|
cost_name cost_value last_update comment default_value
|
|
disk_temptable_create_cost NULL # NULL 20
|
|
disk_temptable_row_cost NULL # NULL 0.5
|
|
key_compare_cost NULL # NULL 0.05
|
|
memory_temptable_create_cost NULL # NULL 1
|
|
memory_temptable_row_cost NULL # NULL 0.1
|
|
row_evaluate_cost NULL # NULL 0.1
|
|
SHOW INDEX FROM mysql.server_cost;
|
|
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible Expression
|
|
server_cost 0 PRIMARY 1 cost_name A # NULL NULL BTREE YES NULL
|
|
SELECT * FROM mysql.engine_cost;
|
|
engine_name device_type cost_name cost_value last_update comment default_value
|
|
default 0 io_block_read_cost NULL # NULL 1
|
|
default 0 memory_block_read_cost NULL # NULL 0.25
|
|
SHOW INDEX FROM mysql.engine_cost;
|
|
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible Expression
|
|
engine_cost 0 PRIMARY 1 cost_name A # NULL NULL BTREE YES NULL
|
|
engine_cost 0 PRIMARY 2 engine_name A # NULL NULL BTREE YES NULL
|
|
engine_cost 0 PRIMARY 3 device_type A # NULL NULL BTREE YES NULL
|
|
UPDATE mysql.server_cost
|
|
SET cost_value=0.1
|
|
WHERE cost_name="row_evaluate_cost";
|
|
SELECT *
|
|
FROM mysql.server_cost
|
|
WHERE cost_name="row_evaluate_cost";
|
|
cost_name cost_value last_update comment default_value
|
|
row_evaluate_cost 0.1 # NULL 0.1
|
|
UPDATE mysql.server_cost
|
|
SET cost_value=DEFAULT
|
|
WHERE cost_name="row_evaluate_cost";
|
|
SELECT *
|
|
FROM mysql.server_cost
|
|
WHERE cost_name="row_evaluate_cost";
|
|
cost_name cost_value last_update comment default_value
|
|
row_evaluate_cost NULL # NULL 0.1
|
|
INSERT INTO mysql.server_cost
|
|
VALUES ("lunch_cost", DEFAULT, CURRENT_TIMESTAMP, "Lunch is important", DEFAULT);
|
|
SELECT * FROM mysql.server_cost;
|
|
cost_name cost_value last_update comment default_value
|
|
disk_temptable_create_cost NULL # NULL 20
|
|
disk_temptable_row_cost NULL # NULL 0.5
|
|
key_compare_cost NULL # NULL 0.05
|
|
lunch_cost NULL # Lunch is important NULL
|
|
memory_temptable_create_cost NULL # NULL 1
|
|
memory_temptable_row_cost NULL # NULL 0.1
|
|
row_evaluate_cost NULL # NULL 0.1
|
|
DELETE FROM mysql.server_cost
|
|
WHERE cost_name="lunch_cost";
|
|
INSERT INTO mysql.server_cost
|
|
VALUES ("row_evaluate_cost", DEFAULT, CURRENT_TIMESTAMP, "Faster CPU", DEFAULT);
|
|
ERROR 23000: Duplicate entry 'row_evaluate_cost' for key 'PRIMARY'
|
|
INSERT INTO mysql.server_cost
|
|
VALUES ("ROW_EVALUATE_COST", DEFAULT, CURRENT_TIMESTAMP, "Faster CPU", DEFAULT);
|
|
ERROR 23000: Duplicate entry 'ROW_EVALUATE_COST' for key 'PRIMARY'
|
|
SELECT * FROM mysql.server_cost;
|
|
cost_name cost_value last_update comment default_value
|
|
disk_temptable_create_cost NULL # NULL 20
|
|
disk_temptable_row_cost NULL # NULL 0.5
|
|
key_compare_cost NULL # NULL 0.05
|
|
memory_temptable_create_cost NULL # NULL 1
|
|
memory_temptable_row_cost NULL # NULL 0.1
|
|
row_evaluate_cost NULL # NULL 0.1
|
|
UPDATE mysql.engine_cost
|
|
SET cost_value=0.1
|
|
WHERE cost_name="io_block_read_cost";
|
|
SELECT *
|
|
FROM mysql.engine_cost
|
|
WHERE cost_name="io_block_read_cost";
|
|
engine_name device_type cost_name cost_value last_update comment default_value
|
|
default 0 io_block_read_cost 0.1 # NULL 1
|
|
UPDATE mysql.engine_cost
|
|
SET cost_value=DEFAULT
|
|
WHERE cost_name="io_block_read_cost";
|
|
SELECT *
|
|
FROM mysql.engine_cost
|
|
WHERE cost_name="io_block_read_cost";
|
|
engine_name device_type cost_name cost_value last_update comment default_value
|
|
default 0 io_block_read_cost NULL # NULL 1
|
|
INSERT INTO mysql.engine_cost
|
|
VALUES ("InnoDB", 2, "lunch_cost1", DEFAULT, CURRENT_TIMESTAMP, "Lunch 1", DEFAULT),
|
|
("InnoDB", 2, "lunch_cost2", DEFAULT, CURRENT_TIMESTAMP, "Lunch 2", DEFAULT);
|
|
SELECT * FROM mysql.engine_cost;
|
|
engine_name device_type cost_name cost_value last_update comment default_value
|
|
default 0 io_block_read_cost NULL # NULL 1
|
|
InnoDB 2 lunch_cost1 NULL # Lunch 1 NULL
|
|
InnoDB 2 lunch_cost2 NULL # Lunch 2 NULL
|
|
default 0 memory_block_read_cost NULL # NULL 0.25
|
|
DELETE FROM mysql.engine_cost
|
|
WHERE cost_name LIKE "lunch_cost%";
|
|
INSERT INTO mysql.engine_cost
|
|
VALUES ("default", 0, "lunch_cost", DEFAULT, CURRENT_TIMESTAMP, "Lunch", DEFAULT);
|
|
INSERT INTO mysql.engine_cost
|
|
VALUES ("default", 0, "lunch_cost", DEFAULT, CURRENT_TIMESTAMP, "Lunch", DEFAULT);
|
|
ERROR 23000: Duplicate entry 'lunch_cost-default-0' for key 'PRIMARY'
|
|
SELECT * FROM mysql.engine_cost;
|
|
engine_name device_type cost_name cost_value last_update comment default_value
|
|
default 0 io_block_read_cost NULL # NULL 1
|
|
default 0 lunch_cost NULL # Lunch NULL
|
|
default 0 memory_block_read_cost NULL # NULL 0.25
|
|
DELETE FROM mysql.engine_cost
|
|
WHERE cost_name="lunch_cost";
|
|
INSERT INTO mysql.engine_cost
|
|
VALUES ("default", 0, "IO_BLOCK_READ_COST", DEFAULT, CURRENT_TIMESTAMP,
|
|
"Lunch", DEFAULT);
|
|
ERROR 23000: Duplicate entry 'IO_BLOCK_READ_COST-default-0' for key 'PRIMARY'
|
|
SELECT * FROM mysql.engine_cost;
|
|
engine_name device_type cost_name cost_value last_update comment default_value
|
|
default 0 io_block_read_cost NULL # NULL 1
|
|
default 0 memory_block_read_cost NULL # NULL 0.25
|
|
CREATE TABLE server_cost_tmp (
|
|
cost_name VARCHAR(64) NOT NULL,
|
|
last_update TIMESTAMP
|
|
);
|
|
CREATE TABLE engine_cost_tmp (
|
|
cost_name VARCHAR(64) NOT NULL,
|
|
last_update TIMESTAMP
|
|
);
|
|
INSERT INTO server_cost_tmp
|
|
SELECT cost_name, last_update FROM mysql.server_cost;
|
|
INSERT INTO engine_cost_tmp
|
|
SELECT cost_name, last_update FROM mysql.engine_cost;
|
|
UPDATE mysql.server_cost
|
|
SET cost_value=0.1
|
|
WHERE cost_name="row_evaluate_cost";
|
|
SELECT mysql.server_cost.cost_name
|
|
FROM mysql.server_cost JOIN server_cost_tmp
|
|
ON mysql.server_cost.cost_name = server_cost_tmp.cost_name
|
|
WHERE mysql.server_cost.last_update > server_cost_tmp.last_update;
|
|
cost_name
|
|
row_evaluate_cost
|
|
UPDATE mysql.server_cost
|
|
SET cost_value=DEFAULT
|
|
WHERE cost_name="row_evaluate_cost";
|
|
UPDATE mysql.engine_cost
|
|
SET cost_value=2.0
|
|
WHERE cost_name="io_block_read_cost";
|
|
SELECT mysql.engine_cost.cost_name
|
|
FROM mysql.engine_cost JOIN engine_cost_tmp
|
|
ON mysql.engine_cost.cost_name = engine_cost_tmp.cost_name
|
|
WHERE mysql.engine_cost.last_update > engine_cost_tmp.last_update;
|
|
cost_name
|
|
io_block_read_cost
|
|
UPDATE mysql.engine_cost
|
|
SET cost_value=DEFAULT
|
|
WHERE cost_name="io_block_read_cost";
|
|
DROP TABLE server_cost_tmp, engine_cost_tmp;
|
|
|
|
WL#10128: Add defaults column to optimizer cost tables
|
|
|
|
Verify that default values are defined for all cost constants
|
|
SELECT COUNT(*) FROM mysql.server_cost WHERE default_value IS NULL;
|
|
COUNT(*)
|
|
0
|
|
SELECT COUNT(*) FROM mysql.engine_cost WHERE default_value IS NULL;
|
|
COUNT(*)
|
|
0
|
|
Verify that default_value columns can not be updated
|
|
UPDATE mysql.server_cost SET default_value = 1.0;
|
|
ERROR HY000: The value specified for generated column 'default_value' in table 'server_cost' is not allowed.
|
|
UPDATE mysql.engine_cost SET default_value = 1.0;
|
|
ERROR HY000: The value specified for generated column 'default_value' in table 'engine_cost' is not allowed.
|