File: 5585.result

package info (click to toggle)
mariadb-10.0 10.0.16-1
  • links: PTS, VCS
  • area: main
  • in suites: jessie-kfreebsd
  • size: 416,512 kB
  • sloc: cpp: 1,351,103; ansic: 803,086; perl: 59,621; pascal: 32,136; sh: 25,156; yacc: 14,897; xml: 5,194; sql: 4,651; cs: 4,647; makefile: 4,113; python: 2,526; ruby: 2,496; lex: 1,427; asm: 295; awk: 54; php: 22; sed: 16
file content (247 lines) | stat: -rw-r--r-- 13,927 bytes parent folder | download
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
drop table if exists t1, t2;
set local default_storage_engine=innodb;
set local storage_engine=innodb;
create table t1 (
file_id bigint unsigned not null auto_increment primary key,
file_number bigint not null);
insert into t1 values (1,1), (2,2), (3,3), (4,4), (5,5), (6,6), (7,7), (8,8), (9,9), (10,10);
insert into t1 select t1.file_id+10, t1.file_number+10 from t1;
insert into t1 select t1.file_id+20, t1.file_number+20 from t1;
insert into t1 select t1.file_id+40, t1.file_number+40 from t1;
insert into t1 select t1.file_id+100, t1.file_number+100 from t1;
insert into t1 select t1.file_id+200, t1.file_number+200 from t1;
insert into t1 select t1.file_id+400, t1.file_number+400 from t1;
insert into t1 select t1.file_id+1000, t1.file_number+1000 from t1;
insert into t1 select t1.file_id+10000, t1.file_number+10000 from t1;
insert into t1 select t1.file_id+100000, t1.file_number+100000 from t1;
insert into t1 select t1.file_id+1000000, t1.file_number+1000000 from t1;
insert into t1 select t1.file_id+10000000, t1.file_number+10000000 from t1;
insert into t1 select t1.file_id+100000000, t1.file_number+100000000 from t1;
create table t2 (
file_id bigint unsigned not null,
country char(2) not null,
hits bigint not null,
insert_ts date not null,
primary key (file_id, insert_ts, country),
key idx2 (insert_ts, country));
insert into t2 select t1.file_id, 'US', 5, now() from t1;
Warnings:
Note	1265	Data truncated for column 'insert_ts' at row 1
Note	1265	Data truncated for column 'insert_ts' at row 2
Note	1265	Data truncated for column 'insert_ts' at row 3
Note	1265	Data truncated for column 'insert_ts' at row 4
Note	1265	Data truncated for column 'insert_ts' at row 5
Note	1265	Data truncated for column 'insert_ts' at row 6
Note	1265	Data truncated for column 'insert_ts' at row 7
Note	1265	Data truncated for column 'insert_ts' at row 8
Note	1265	Data truncated for column 'insert_ts' at row 9
Note	1265	Data truncated for column 'insert_ts' at row 10
Note	1265	Data truncated for column 'insert_ts' at row 11
Note	1265	Data truncated for column 'insert_ts' at row 12
Note	1265	Data truncated for column 'insert_ts' at row 13
Note	1265	Data truncated for column 'insert_ts' at row 14
Note	1265	Data truncated for column 'insert_ts' at row 15
Note	1265	Data truncated for column 'insert_ts' at row 16
Note	1265	Data truncated for column 'insert_ts' at row 17
Note	1265	Data truncated for column 'insert_ts' at row 18
Note	1265	Data truncated for column 'insert_ts' at row 19
Note	1265	Data truncated for column 'insert_ts' at row 20
Note	1265	Data truncated for column 'insert_ts' at row 21
Note	1265	Data truncated for column 'insert_ts' at row 22
Note	1265	Data truncated for column 'insert_ts' at row 23
Note	1265	Data truncated for column 'insert_ts' at row 24
Note	1265	Data truncated for column 'insert_ts' at row 25
Note	1265	Data truncated for column 'insert_ts' at row 26
Note	1265	Data truncated for column 'insert_ts' at row 27
Note	1265	Data truncated for column 'insert_ts' at row 28
Note	1265	Data truncated for column 'insert_ts' at row 29
Note	1265	Data truncated for column 'insert_ts' at row 30
Note	1265	Data truncated for column 'insert_ts' at row 31
Note	1265	Data truncated for column 'insert_ts' at row 32
Note	1265	Data truncated for column 'insert_ts' at row 33
Note	1265	Data truncated for column 'insert_ts' at row 34
Note	1265	Data truncated for column 'insert_ts' at row 35
Note	1265	Data truncated for column 'insert_ts' at row 36
Note	1265	Data truncated for column 'insert_ts' at row 37
Note	1265	Data truncated for column 'insert_ts' at row 38
Note	1265	Data truncated for column 'insert_ts' at row 39
Note	1265	Data truncated for column 'insert_ts' at row 40
Note	1265	Data truncated for column 'insert_ts' at row 41
Note	1265	Data truncated for column 'insert_ts' at row 42
Note	1265	Data truncated for column 'insert_ts' at row 43
Note	1265	Data truncated for column 'insert_ts' at row 44
Note	1265	Data truncated for column 'insert_ts' at row 45
Note	1265	Data truncated for column 'insert_ts' at row 46
Note	1265	Data truncated for column 'insert_ts' at row 47
Note	1265	Data truncated for column 'insert_ts' at row 48
Note	1265	Data truncated for column 'insert_ts' at row 49
Note	1265	Data truncated for column 'insert_ts' at row 50
Note	1265	Data truncated for column 'insert_ts' at row 51
Note	1265	Data truncated for column 'insert_ts' at row 52
Note	1265	Data truncated for column 'insert_ts' at row 53
Note	1265	Data truncated for column 'insert_ts' at row 54
Note	1265	Data truncated for column 'insert_ts' at row 55
Note	1265	Data truncated for column 'insert_ts' at row 56
Note	1265	Data truncated for column 'insert_ts' at row 57
Note	1265	Data truncated for column 'insert_ts' at row 58
Note	1265	Data truncated for column 'insert_ts' at row 59
Note	1265	Data truncated for column 'insert_ts' at row 60
Note	1265	Data truncated for column 'insert_ts' at row 61
Note	1265	Data truncated for column 'insert_ts' at row 62
Note	1265	Data truncated for column 'insert_ts' at row 63
Note	1265	Data truncated for column 'insert_ts' at row 64
insert into t2 select t1.file_id, 'CA', 5, now() from t1;
Warnings:
Note	1265	Data truncated for column 'insert_ts' at row 1
Note	1265	Data truncated for column 'insert_ts' at row 2
Note	1265	Data truncated for column 'insert_ts' at row 3
Note	1265	Data truncated for column 'insert_ts' at row 4
Note	1265	Data truncated for column 'insert_ts' at row 5
Note	1265	Data truncated for column 'insert_ts' at row 6
Note	1265	Data truncated for column 'insert_ts' at row 7
Note	1265	Data truncated for column 'insert_ts' at row 8
Note	1265	Data truncated for column 'insert_ts' at row 9
Note	1265	Data truncated for column 'insert_ts' at row 10
Note	1265	Data truncated for column 'insert_ts' at row 11
Note	1265	Data truncated for column 'insert_ts' at row 12
Note	1265	Data truncated for column 'insert_ts' at row 13
Note	1265	Data truncated for column 'insert_ts' at row 14
Note	1265	Data truncated for column 'insert_ts' at row 15
Note	1265	Data truncated for column 'insert_ts' at row 16
Note	1265	Data truncated for column 'insert_ts' at row 17
Note	1265	Data truncated for column 'insert_ts' at row 18
Note	1265	Data truncated for column 'insert_ts' at row 19
Note	1265	Data truncated for column 'insert_ts' at row 20
Note	1265	Data truncated for column 'insert_ts' at row 21
Note	1265	Data truncated for column 'insert_ts' at row 22
Note	1265	Data truncated for column 'insert_ts' at row 23
Note	1265	Data truncated for column 'insert_ts' at row 24
Note	1265	Data truncated for column 'insert_ts' at row 25
Note	1265	Data truncated for column 'insert_ts' at row 26
Note	1265	Data truncated for column 'insert_ts' at row 27
Note	1265	Data truncated for column 'insert_ts' at row 28
Note	1265	Data truncated for column 'insert_ts' at row 29
Note	1265	Data truncated for column 'insert_ts' at row 30
Note	1265	Data truncated for column 'insert_ts' at row 31
Note	1265	Data truncated for column 'insert_ts' at row 32
Note	1265	Data truncated for column 'insert_ts' at row 33
Note	1265	Data truncated for column 'insert_ts' at row 34
Note	1265	Data truncated for column 'insert_ts' at row 35
Note	1265	Data truncated for column 'insert_ts' at row 36
Note	1265	Data truncated for column 'insert_ts' at row 37
Note	1265	Data truncated for column 'insert_ts' at row 38
Note	1265	Data truncated for column 'insert_ts' at row 39
Note	1265	Data truncated for column 'insert_ts' at row 40
Note	1265	Data truncated for column 'insert_ts' at row 41
Note	1265	Data truncated for column 'insert_ts' at row 42
Note	1265	Data truncated for column 'insert_ts' at row 43
Note	1265	Data truncated for column 'insert_ts' at row 44
Note	1265	Data truncated for column 'insert_ts' at row 45
Note	1265	Data truncated for column 'insert_ts' at row 46
Note	1265	Data truncated for column 'insert_ts' at row 47
Note	1265	Data truncated for column 'insert_ts' at row 48
Note	1265	Data truncated for column 'insert_ts' at row 49
Note	1265	Data truncated for column 'insert_ts' at row 50
Note	1265	Data truncated for column 'insert_ts' at row 51
Note	1265	Data truncated for column 'insert_ts' at row 52
Note	1265	Data truncated for column 'insert_ts' at row 53
Note	1265	Data truncated for column 'insert_ts' at row 54
Note	1265	Data truncated for column 'insert_ts' at row 55
Note	1265	Data truncated for column 'insert_ts' at row 56
Note	1265	Data truncated for column 'insert_ts' at row 57
Note	1265	Data truncated for column 'insert_ts' at row 58
Note	1265	Data truncated for column 'insert_ts' at row 59
Note	1265	Data truncated for column 'insert_ts' at row 60
Note	1265	Data truncated for column 'insert_ts' at row 61
Note	1265	Data truncated for column 'insert_ts' at row 62
Note	1265	Data truncated for column 'insert_ts' at row 63
Note	1265	Data truncated for column 'insert_ts' at row 64
insert into t2 select t1.file_id, 'JP', 5, now() from t1;
Warnings:
Note	1265	Data truncated for column 'insert_ts' at row 1
Note	1265	Data truncated for column 'insert_ts' at row 2
Note	1265	Data truncated for column 'insert_ts' at row 3
Note	1265	Data truncated for column 'insert_ts' at row 4
Note	1265	Data truncated for column 'insert_ts' at row 5
Note	1265	Data truncated for column 'insert_ts' at row 6
Note	1265	Data truncated for column 'insert_ts' at row 7
Note	1265	Data truncated for column 'insert_ts' at row 8
Note	1265	Data truncated for column 'insert_ts' at row 9
Note	1265	Data truncated for column 'insert_ts' at row 10
Note	1265	Data truncated for column 'insert_ts' at row 11
Note	1265	Data truncated for column 'insert_ts' at row 12
Note	1265	Data truncated for column 'insert_ts' at row 13
Note	1265	Data truncated for column 'insert_ts' at row 14
Note	1265	Data truncated for column 'insert_ts' at row 15
Note	1265	Data truncated for column 'insert_ts' at row 16
Note	1265	Data truncated for column 'insert_ts' at row 17
Note	1265	Data truncated for column 'insert_ts' at row 18
Note	1265	Data truncated for column 'insert_ts' at row 19
Note	1265	Data truncated for column 'insert_ts' at row 20
Note	1265	Data truncated for column 'insert_ts' at row 21
Note	1265	Data truncated for column 'insert_ts' at row 22
Note	1265	Data truncated for column 'insert_ts' at row 23
Note	1265	Data truncated for column 'insert_ts' at row 24
Note	1265	Data truncated for column 'insert_ts' at row 25
Note	1265	Data truncated for column 'insert_ts' at row 26
Note	1265	Data truncated for column 'insert_ts' at row 27
Note	1265	Data truncated for column 'insert_ts' at row 28
Note	1265	Data truncated for column 'insert_ts' at row 29
Note	1265	Data truncated for column 'insert_ts' at row 30
Note	1265	Data truncated for column 'insert_ts' at row 31
Note	1265	Data truncated for column 'insert_ts' at row 32
Note	1265	Data truncated for column 'insert_ts' at row 33
Note	1265	Data truncated for column 'insert_ts' at row 34
Note	1265	Data truncated for column 'insert_ts' at row 35
Note	1265	Data truncated for column 'insert_ts' at row 36
Note	1265	Data truncated for column 'insert_ts' at row 37
Note	1265	Data truncated for column 'insert_ts' at row 38
Note	1265	Data truncated for column 'insert_ts' at row 39
Note	1265	Data truncated for column 'insert_ts' at row 40
Note	1265	Data truncated for column 'insert_ts' at row 41
Note	1265	Data truncated for column 'insert_ts' at row 42
Note	1265	Data truncated for column 'insert_ts' at row 43
Note	1265	Data truncated for column 'insert_ts' at row 44
Note	1265	Data truncated for column 'insert_ts' at row 45
Note	1265	Data truncated for column 'insert_ts' at row 46
Note	1265	Data truncated for column 'insert_ts' at row 47
Note	1265	Data truncated for column 'insert_ts' at row 48
Note	1265	Data truncated for column 'insert_ts' at row 49
Note	1265	Data truncated for column 'insert_ts' at row 50
Note	1265	Data truncated for column 'insert_ts' at row 51
Note	1265	Data truncated for column 'insert_ts' at row 52
Note	1265	Data truncated for column 'insert_ts' at row 53
Note	1265	Data truncated for column 'insert_ts' at row 54
Note	1265	Data truncated for column 'insert_ts' at row 55
Note	1265	Data truncated for column 'insert_ts' at row 56
Note	1265	Data truncated for column 'insert_ts' at row 57
Note	1265	Data truncated for column 'insert_ts' at row 58
Note	1265	Data truncated for column 'insert_ts' at row 59
Note	1265	Data truncated for column 'insert_ts' at row 60
Note	1265	Data truncated for column 'insert_ts' at row 61
Note	1265	Data truncated for column 'insert_ts' at row 62
Note	1265	Data truncated for column 'insert_ts' at row 63
Note	1265	Data truncated for column 'insert_ts' at row 64
insert into t2 select t2.file_id, t2.country, 5, date_sub(t2.insert_ts,interval 1 day) from t2;
insert into t2 select t2.file_id, t2.country, 5, date_sub(t2.insert_ts,interval 2 day) from t2;
insert into t2 select t2.file_id, t2.country, 5, date_sub(t2.insert_ts,interval 4 day) from t2;
insert into t2 select t2.file_id, t2.country, 5, date_sub(t2.insert_ts,interval 8 day) from t2;
insert into t2 select t2.file_id, t2.country, 5, date_sub(t2.insert_ts,interval 16 day) from t2;
explain
select t1.file_id, 
t1.file_number, 
(select hits from t2 where t2.file_id = t1.file_id and t2.insert_ts = date(date_sub(now(),interval 1 day))) as d
from t1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	NULL;	
2	DEPENDENT SUBQUERY	t2	ref	PRIMARY,idx2	PRIMARY	11	test.t1.file_id,const	NULL;	Using where
alter table t2 engine=tokudb;
explain
select t1.file_id, 
t1.file_number, 
(select hits from t2 where t2.file_id = t1.file_id and t2.insert_ts = date(date_sub(now(),interval 1 day))) as d
from t1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	NULL;	
2	DEPENDENT SUBQUERY	t2	ref	PRIMARY,idx2	PRIMARY	11	test.t1.file_id,const	NULL;	Using where
drop table if exists t1, t2;