summaryrefslogtreecommitdiff
path: root/mysql-test/main/stat_tables_par.result
blob: 7a95e8259b69ce35108e18f2c184c7932217198d (plain)
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
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
# restart
set @save_use_stat_tables=@@use_stat_tables;
set use_stat_tables='preferably';
DROP DATABASE IF EXISTS dbt3_s001;
CREATE DATABASE dbt3_s001;
use dbt3_s001;
set @save_optimizer_switch=@@optimizer_switch;
set optimizer_switch='extended_keys=off';
select * from mysql.table_stats;
db_name	table_name	cardinality
dbt3_s001	customer	150
dbt3_s001	lineitem	6005
dbt3_s001	nation	25
dbt3_s001	orders	1500
dbt3_s001	part	200
dbt3_s001	partsupp	700
dbt3_s001	region	5
dbt3_s001	supplier	10
select * from mysql.index_stats;
db_name	table_name	index_name	prefix_arity	avg_frequency
dbt3_s001	customer	PRIMARY	1	1.0000
dbt3_s001	customer	i_c_nationkey	1	6.0000
dbt3_s001	lineitem	PRIMARY	1	4.0033
dbt3_s001	lineitem	PRIMARY	2	1.0000
dbt3_s001	lineitem	i_l_shipdate	1	2.6500
dbt3_s001	lineitem	i_l_suppkey_partkey	1	30.0250
dbt3_s001	lineitem	i_l_suppkey_partkey	2	8.5786
dbt3_s001	lineitem	i_l_partkey	1	30.0250
dbt3_s001	lineitem	i_l_suppkey	1	600.5000
dbt3_s001	lineitem	i_l_receiptdate	1	2.6477
dbt3_s001	lineitem	i_l_orderkey	1	4.0033
dbt3_s001	lineitem	i_l_orderkey_quantity	1	4.0033
dbt3_s001	lineitem	i_l_orderkey_quantity	2	1.0404
dbt3_s001	lineitem	i_l_commitdate	1	2.7160
dbt3_s001	nation	PRIMARY	1	1.0000
dbt3_s001	nation	i_n_regionkey	1	5.0000
dbt3_s001	orders	PRIMARY	1	1.0000
dbt3_s001	orders	i_o_orderdate	1	1.3321
dbt3_s001	orders	i_o_custkey	1	15.0000
dbt3_s001	part	PRIMARY	1	1.0000
dbt3_s001	partsupp	PRIMARY	1	3.5000
dbt3_s001	partsupp	PRIMARY	2	1.0000
dbt3_s001	partsupp	i_ps_partkey	1	3.5000
dbt3_s001	partsupp	i_ps_suppkey	1	70.0000
dbt3_s001	region	PRIMARY	1	1.0000
dbt3_s001	supplier	PRIMARY	1	1.0000
dbt3_s001	supplier	i_s_nationkey	1	1.1111
flush table lineitem;
set use_stat_tables='never';
select round(sum(l_extendedprice*l_discount),4) as revenue
from lineitem
where l_shipdate >= date '1994-01-01' 
and l_shipdate < date '1994-01-01' + interval '1' year
and l_discount between 0.06 - 0.01 and 0.06 + 0.01
and l_quantity < 24;
revenue
77949.9186
connect  con1, localhost, root,,;
connect  con2, localhost, root,,;
connection con1;
set debug_sync='statistics_mem_alloc_start1  WAIT_FOR second_thread_started_too';
set debug_sync='statistics_mem_alloc_start2  SIGNAL first_thread_working';
use dbt3_s001;
set use_stat_tables='preferably';
select round(sum(l_extendedprice*l_discount),4) as revenue
from lineitem
where l_shipdate >= date '1994-01-01' 
and l_shipdate < date '1994-01-01' + interval '1' year
and l_discount between 0.06 - 0.01 and 0.06 + 0.01
and l_quantity < 24                                            ;
connection con2;
set debug_sync='statistics_mem_alloc_start1  SIGNAL second_thread_started_too';
set debug_sync='statistics_mem_alloc_start2  WAIT_FOR first_thread_working';
use dbt3_s001;
set use_stat_tables='preferably';
select round(sum(l_extendedprice*l_discount),4) as revenue
from lineitem
where l_shipdate >= date '1994-01-01' 
and l_shipdate < date '1994-01-01' + interval '1' year
and l_discount between 0.06 - 0.01 and 0.06 + 0.01
and l_quantity < 24;
connection con1;
revenue
77949.9186
connection con2;
revenue
77949.9186
connection default;
set use_stat_tables='preferably';
disconnect con1;
disconnect con2;
set debug_sync='RESET';
select * from mysql.index_stats where table_name='lineitem' order by index_name;
db_name	table_name	index_name	prefix_arity	avg_frequency
dbt3_s001	lineitem	PRIMARY	1	4.0033
dbt3_s001	lineitem	PRIMARY	2	1.0000
dbt3_s001	lineitem	i_l_commitdate	1	2.7160
dbt3_s001	lineitem	i_l_orderkey	1	4.0033
dbt3_s001	lineitem	i_l_orderkey_quantity	1	4.0033
dbt3_s001	lineitem	i_l_orderkey_quantity	2	1.0404
dbt3_s001	lineitem	i_l_partkey	1	30.0250
dbt3_s001	lineitem	i_l_receiptdate	1	2.6477
dbt3_s001	lineitem	i_l_shipdate	1	2.6500
dbt3_s001	lineitem	i_l_suppkey	1	600.5000
dbt3_s001	lineitem	i_l_suppkey_partkey	1	30.0250
dbt3_s001	lineitem	i_l_suppkey_partkey	2	8.5786
delete from mysql.index_stats 
where table_name='lineitem' and
index_name in ('i_l_shipdate', 'i_l_receiptdate');
select * from mysql.index_stats where table_name='lineitem' order by index_name;
db_name	table_name	index_name	prefix_arity	avg_frequency
dbt3_s001	lineitem	PRIMARY	1	4.0033
dbt3_s001	lineitem	PRIMARY	2	1.0000
dbt3_s001	lineitem	i_l_commitdate	1	2.7160
dbt3_s001	lineitem	i_l_orderkey	1	4.0033
dbt3_s001	lineitem	i_l_orderkey_quantity	1	4.0033
dbt3_s001	lineitem	i_l_orderkey_quantity	2	1.0404
dbt3_s001	lineitem	i_l_partkey	1	30.0250
dbt3_s001	lineitem	i_l_suppkey	1	600.5000
dbt3_s001	lineitem	i_l_suppkey_partkey	2	8.5786
dbt3_s001	lineitem	i_l_suppkey_partkey	1	30.0250
analyze table lineitem persistent for columns() indexes (i_l_shipdate);
select * from mysql.index_stats where table_name='lineitem' order by index_name;
db_name	table_name	index_name	prefix_arity	avg_frequency
dbt3_s001	lineitem	PRIMARY	1	4.0033
dbt3_s001	lineitem	PRIMARY	2	1.0000
dbt3_s001	lineitem	i_l_commitdate	1	2.7160
dbt3_s001	lineitem	i_l_orderkey	1	4.0033
dbt3_s001	lineitem	i_l_orderkey_quantity	1	4.0033
dbt3_s001	lineitem	i_l_orderkey_quantity	2	1.0404
dbt3_s001	lineitem	i_l_partkey	1	30.0250
dbt3_s001	lineitem	i_l_shipdate	1	2.6500
dbt3_s001	lineitem	i_l_suppkey	1	600.5000
dbt3_s001	lineitem	i_l_suppkey_partkey	2	8.5786
dbt3_s001	lineitem	i_l_suppkey_partkey	1	30.0250
delete from mysql.index_stats 
where table_name='lineitem' and index_name= 'i_l_shipdate';
select * from mysql.index_stats where table_name='lineitem' order by index_name;
db_name	table_name	index_name	prefix_arity	avg_frequency
dbt3_s001	lineitem	PRIMARY	1	4.0033
dbt3_s001	lineitem	PRIMARY	2	1.0000
dbt3_s001	lineitem	i_l_commitdate	1	2.7160
dbt3_s001	lineitem	i_l_orderkey	1	4.0033
dbt3_s001	lineitem	i_l_orderkey_quantity	1	4.0033
dbt3_s001	lineitem	i_l_orderkey_quantity	2	1.0404
dbt3_s001	lineitem	i_l_partkey	1	30.0250
dbt3_s001	lineitem	i_l_suppkey	1	600.5000
dbt3_s001	lineitem	i_l_suppkey_partkey	2	8.5786
dbt3_s001	lineitem	i_l_suppkey_partkey	1	30.0250
connect  con1, localhost, root,,;
connect  con2, localhost, root,,;
connection con1;
set debug_sync='statistics_collection_start1  WAIT_FOR second_thread_started_too';
set debug_sync='statistics_collection_start2  SIGNAL first_thread_working';
use dbt3_s001;
set use_stat_tables='preferably';
analyze table lineitem persistent for columns() indexes (i_l_shipdate);
connection con2;
set debug_sync='statistics_collection_start1  SIGNAL second_thread_started_too';
set debug_sync='statistics_collection_start2  WAIT_FOR first_thread_working';
use dbt3_s001;
set use_stat_tables='preferably';
analyze table lineitem persistent for columns() indexes (i_l_receiptdate);
connection con1;
connection con2;
connection default;
disconnect con1;
disconnect con2;
set debug_sync='RESET';
select * from mysql.index_stats where table_name='lineitem' order by index_name, prefix_arity;
db_name	table_name	index_name	prefix_arity	avg_frequency
dbt3_s001	lineitem	PRIMARY	1	4.0033
dbt3_s001	lineitem	PRIMARY	2	1.0000
dbt3_s001	lineitem	i_l_commitdate	1	2.7160
dbt3_s001	lineitem	i_l_orderkey	1	4.0033
dbt3_s001	lineitem	i_l_orderkey_quantity	1	4.0033
dbt3_s001	lineitem	i_l_orderkey_quantity	2	1.0404
dbt3_s001	lineitem	i_l_partkey	1	30.0250
dbt3_s001	lineitem	i_l_receiptdate	1	2.6477
dbt3_s001	lineitem	i_l_shipdate	1	2.6500
dbt3_s001	lineitem	i_l_suppkey	1	600.5000
dbt3_s001	lineitem	i_l_suppkey_partkey	1	30.0250
dbt3_s001	lineitem	i_l_suppkey_partkey	2	8.5786
select * from mysql.index_stats where table_name='lineitem'
 order by index_name, prefix_arity;
db_name	table_name	index_name	prefix_arity	avg_frequency
dbt3_s001	lineitem	PRIMARY	1	4.0033
dbt3_s001	lineitem	PRIMARY	2	1.0000
dbt3_s001	lineitem	i_l_commitdate	1	2.7160
dbt3_s001	lineitem	i_l_orderkey	1	4.0033
dbt3_s001	lineitem	i_l_orderkey_quantity	1	4.0033
dbt3_s001	lineitem	i_l_orderkey_quantity	2	1.0404
dbt3_s001	lineitem	i_l_partkey	1	30.0250
dbt3_s001	lineitem	i_l_receiptdate	1	2.6477
dbt3_s001	lineitem	i_l_shipdate	1	2.6500
dbt3_s001	lineitem	i_l_suppkey	1	600.5000
dbt3_s001	lineitem	i_l_suppkey_partkey	1	30.0250
dbt3_s001	lineitem	i_l_suppkey_partkey	2	8.5786
set debug_sync='RESET';
connect  con1, localhost, root,,;
connect  con2, localhost, root,,;
connection con1;
set debug_sync='statistics_collection_start SIGNAL parked WAIT_FOR finish';
use dbt3_s001;
set use_stat_tables='preferably';
analyze table lineitem persistent for all;
connection con2;
set debug_sync='now WAIT_FOR parked';
use dbt3_s001;
set use_stat_tables='never';
set debug_sync='now SIGNAL finish';
connection con1;
connection default;
disconnect con1;
disconnect con2;
set debug_sync='RESET';
select * from mysql.index_stats where table_name='lineitem'
  order by index_name, prefix_arity;
db_name	table_name	index_name	prefix_arity	avg_frequency
dbt3_s001	lineitem	PRIMARY	1	4.0033
dbt3_s001	lineitem	PRIMARY	2	1.0000
dbt3_s001	lineitem	i_l_commitdate	1	2.7160
dbt3_s001	lineitem	i_l_orderkey	1	4.0033
dbt3_s001	lineitem	i_l_orderkey_quantity	1	4.0033
dbt3_s001	lineitem	i_l_orderkey_quantity	2	1.0404
dbt3_s001	lineitem	i_l_partkey	1	30.0250
dbt3_s001	lineitem	i_l_receiptdate	1	2.6477
dbt3_s001	lineitem	i_l_shipdate	1	2.6500
dbt3_s001	lineitem	i_l_suppkey	1	600.5000
dbt3_s001	lineitem	i_l_suppkey_partkey	1	30.0250
dbt3_s001	lineitem	i_l_suppkey_partkey	2	8.5786
set @save_global_use_stat_tables=@@global.use_stat_tables;
set global use_stat_tables='preferably';
set debug_sync='RESET';
connect  con1, localhost, root,,;
connect  con2, localhost, root,,;
connection con1;
set debug_sync='statistics_update_start SIGNAL parker WAIT_FOR go1 EXECUTE 1';
set debug_sync='thr_multi_lock_before_thr_lock SIGNAL go2 EXECUTE 3';
use dbt3_s001;
analyze table lineitem persistent for all;
connection con2;
set debug_sync='open_and_process_table WAIT_FOR parker';
set debug_sync='statistics_read_start SIGNAL go1 WAIT_FOR go2';
use dbt3_s001;
select * from mysql.index_stats, lineitem where index_name= 'i_l_shipdate' and l_orderkey=1 and l_partkey=68 order by prefix_arity;;
connection con1;
connection con2;
db_name	table_name	index_name	prefix_arity	avg_frequency	l_orderkey	l_partkey	l_suppkey	l_linenumber	l_quantity	l_extendedprice	l_discount	l_tax	l_returnflag	l_linestatus	l_shipDATE	l_commitDATE	l_receiptDATE	l_shipinstruct	l_shipmode	l_comment
dbt3_s001	lineitem	i_l_shipdate	1	2.6500	1	68	9	2	36	34850.16	0.09	0.06	N	O	1996-04-12	1996-02-28	1996-04-20	TAKE BACK RETURN	MAIL	slyly bold pinto beans detect s
connection default;
disconnect con1;
disconnect con2;
set debug_sync='RESET';
set global use_stat_tables=@save_global_use_stat_tables;
DROP DATABASE dbt3_s001;
use test;
set @save_global_use_stat_tables=@@global.use_stat_tables;
set global use_stat_tables='preferably';
set debug_sync='RESET';
create table t1 (a int, b int, key(a));
insert t1 values (1,1),(2,2);
analyze table t1;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	Engine-independent statistics collected
test.t1	analyze	status	OK
SET debug_sync='after_open_table_ignore_flush WAIT_FOR go';
select * from information_schema.statistics where table_schema='test';
connect con1, localhost, root;
connection con1;
select * from t1;
a	b
1	1
2	2
SET DEBUG_SYNC= "now SIGNAL go";
connection default;
TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	NON_UNIQUE	INDEX_SCHEMA	INDEX_NAME	SEQ_IN_INDEX	COLUMN_NAME	COLLATION	CARDINALITY	SUB_PART	PACKED	NULLABLE	INDEX_TYPE	COMMENT	INDEX_COMMENT	IGNORED
def	test	t1	1	test	a	1	a	A	2	NULL	NULL	YES	BTREE			NO
connection default;
disconnect con1;
set debug_sync='RESET';
drop table t1;
set global use_stat_tables=@save_global_use_stat_tables;
set use_stat_tables=@save_use_stat_tables;