1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
|
SELECT cost_name,cost_value FROM mysql.server_cost;
cost_name cost_value
disk_temptable_create_cost NULL
disk_temptable_row_cost NULL
key_compare_cost NULL
memory_temptable_create_cost NULL
memory_temptable_row_cost NULL
row_evaluate_cost NULL
SELECT engine_name,cost_name,cost_value FROM mysql.engine_cost;
engine_name cost_name cost_value
default io_block_read_cost NULL
default memory_block_read_cost NULL
CREATE TABLE t0 (
i1 INTEGER
);
INSERT INTO t0 VALUE (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
CREATE TABLE t1_innodb (
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=InnoDB;
# Since ANALYZE TABLE only reads a subset of the data, the statistics for
# table t1 depends on the row order. And since the INSERT INTO ... SELECT
# may be executed using different execution plans, we've added ORDER BY
# to ensure that we rows has the same order every time. If not, the
# estimated number of rows in EXPLAIN may change on different platforms.
INSERT INTO t1_innodb
SELECT a0.i1 + 10 * a1.i1, a0.i1, 'abc', 'def', 'ghi', 'jkl'
FROM t0 AS a0, t0 AS a1 ORDER BY a0.i1, a1.i1;
ANALYZE TABLE t1_innodb;
Table Op Msg_type Msg_text
test.t1_innodb analyze status OK
"Explain with cost estimate against InnoDB"
EXPLAIN FORMAT=JSON SELECT * FROM t1_innodb;
EXPLAIN
{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "12.75"
},
"table": {
"table_name": "t1_innodb",
"access_type": "ALL",
"rows_examined_per_scan": 100,
"rows_produced_per_join": 100,
"filtered": "100.00",
"cost_info": {
"read_cost": "2.75",
"eval_cost": "10.00",
"prefix_cost": "12.75",
"data_read_per_join": "392K"
},
"used_columns": [
"pk",
"i1",
"c1",
"c2",
"c3",
"c4"
]
}
}
}
Warnings:
Note 1003 /* select#1 */ select `test`.`t1_innodb`.`pk` AS `pk`,`test`.`t1_innodb`.`i1` AS `i1`,`test`.`t1_innodb`.`c1` AS `c1`,`test`.`t1_innodb`.`c2` AS `c2`,`test`.`t1_innodb`.`c3` AS `c3`,`test`.`t1_innodb`.`c4` AS `c4` from `test`.`t1_innodb`
UPDATE mysql.server_cost
SET cost_value=0.4
WHERE cost_name="row_evaluate_cost";
UPDATE mysql.engine_cost
SET cost_value=2.0
WHERE cost_name="memory_block_read_cost";
UPDATE mysql.engine_cost
SET cost_value=2.0
WHERE cost_name="io_block_read_cost";
"Restarting MySQL server"
# restart
"MySQL restarted"
SELECT cost_name, cost_value FROM mysql.server_cost;
cost_name cost_value
disk_temptable_create_cost NULL
disk_temptable_row_cost NULL
key_compare_cost NULL
memory_temptable_create_cost NULL
memory_temptable_row_cost NULL
row_evaluate_cost 0.4
SELECT engine_name, cost_name, cost_value FROM mysql.engine_cost;
engine_name cost_name cost_value
default io_block_read_cost 2
default memory_block_read_cost 2
"Explain with cost estimate against MyISAM"
EXPLAIN FORMAT=JSON SELECT * FROM t1_innodb;
EXPLAIN
{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "62.00"
},
"table": {
"table_name": "t1_innodb",
"access_type": "ALL",
"rows_examined_per_scan": 100,
"rows_produced_per_join": 100,
"filtered": "100.00",
"cost_info": {
"read_cost": "22.00",
"eval_cost": "40.00",
"prefix_cost": "62.00",
"data_read_per_join": "392K"
},
"used_columns": [
"pk",
"i1",
"c1",
"c2",
"c3",
"c4"
]
}
}
}
Warnings:
Note 1003 /* select#1 */ select `test`.`t1_innodb`.`pk` AS `pk`,`test`.`t1_innodb`.`i1` AS `i1`,`test`.`t1_innodb`.`c1` AS `c1`,`test`.`t1_innodb`.`c2` AS `c2`,`test`.`t1_innodb`.`c3` AS `c3`,`test`.`t1_innodb`.`c4` AS `c4` from `test`.`t1_innodb`
UPDATE mysql.server_cost
SET cost_value=DEFAULT;
UPDATE mysql.engine_cost
SET cost_value=DEFAULT;
INSERT INTO mysql.engine_cost VALUES
("InnoDB", 0, "memory_block_read_cost", 4.0, CURRENT_TIMESTAMP, DEFAULT, DEFAULT);
INSERT INTO mysql.engine_cost VALUES
("InnoDB", 0, "io_block_read_cost", 4.0, CURRENT_TIMESTAMP, DEFAULT, DEFAULT);
"Restarting MySQL server"
# restart
"MySQL restarted"
SELECT cost_name, cost_value FROM mysql.server_cost;
cost_name cost_value
disk_temptable_create_cost NULL
disk_temptable_row_cost NULL
key_compare_cost NULL
memory_temptable_create_cost NULL
memory_temptable_row_cost NULL
row_evaluate_cost NULL
SELECT engine_name, cost_name, cost_value FROM mysql.engine_cost;
engine_name cost_name cost_value
default io_block_read_cost NULL
InnoDB io_block_read_cost 4
default memory_block_read_cost NULL
InnoDB memory_block_read_cost 4
"Explain with cost estimate against InnoDB"
EXPLAIN FORMAT=JSON SELECT * FROM t1_innodb;
EXPLAIN
{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "54.00"
},
"table": {
"table_name": "t1_innodb",
"access_type": "ALL",
"rows_examined_per_scan": 100,
"rows_produced_per_join": 100,
"filtered": "100.00",
"cost_info": {
"read_cost": "44.00",
"eval_cost": "10.00",
"prefix_cost": "54.00",
"data_read_per_join": "392K"
},
"used_columns": [
"pk",
"i1",
"c1",
"c2",
"c3",
"c4"
]
}
}
}
Warnings:
Note 1003 /* select#1 */ select `test`.`t1_innodb`.`pk` AS `pk`,`test`.`t1_innodb`.`i1` AS `i1`,`test`.`t1_innodb`.`c1` AS `c1`,`test`.`t1_innodb`.`c2` AS `c2`,`test`.`t1_innodb`.`c3` AS `c3`,`test`.`t1_innodb`.`c4` AS `c4` from `test`.`t1_innodb`
DELETE FROM mysql.engine_cost
WHERE engine_name NOT LIKE "default";
"Restarting MySQL server"
# restart
DROP TABLE t0, t1_innodb;
|
