2
# Copyright (C) 2001, 2003 MySQL AB
4
# This library is free software; you can redistribute it and/or
5
# modify it under the terms of the GNU Library General Public
6
# License as published by the Free Software Foundation; version 2
9
# This library is distributed in the hope that it will be useful,
10
# but WITHOUT ANY WARRANTY; without even the implied warranty of
11
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
12
# Library General Public License for more details.
14
# You should have received a copy of the GNU Library General Public
15
# License along with this library; if not, write to the Free
16
# Software Foundation, Inc., 59 Temple Place - Suite 330, Boston,
19
# Test of selecting on keys that consist of many parts
21
##################### Standard benchmark inits ##############################
28
$opt_loop_count=10000;
29
$opt_medium_loop_count=200;
30
$opt_small_loop_count=10;
34
$pwd = cwd(); $pwd = "." if ($pwd eq '');
35
require "$pwd/bench-init.pl" || die "Can't read Configuration file: $!\n";
37
$columns=min($limits->{'max_columns'},500,($limits->{'query_size'}-50)/24,
38
$limits->{'max_conditions'}/2-3);
43
$opt_medium_loop_count/=10;
44
$opt_small_loop_count/=10;
48
print "Testing the speed of selecting on keys that consist of many parts\n";
49
print "The test-table has $opt_loop_count rows and the test is done with $columns ranges.\n\n";
52
#### Connect and start timeing
55
$dbh = $server->connect();
56
$start_time=new Benchmark;
59
#### Create needed tables
62
goto select_test if ($opt_skip_create);
64
print "Creating table\n";
65
$dbh->do("drop table bench1" . $server->{'drop_attr'});
67
do_many($dbh,$server->create("bench1",
68
["region char(1) NOT NULL",
69
"idn integer NOT NULL",
70
"rev_idn integer NOT NULL",
71
"grp integer NOT NULL"],
72
["primary key (region,idn)",
73
"unique (region,rev_idn)",
74
"unique (region,grp,idn)"]));
77
do_query($dbh,"LOCK TABLES bench1 WRITE");
80
if ($opt_fast && defined($server->{vacuum}))
82
$server->vacuum(1,\$dbh);
86
#### Insert $opt_loop_count records with
87
#### region: "A" -> "E"
89
#### rev_idn: count -> 0,
90
#### grp: distributed values 0 - > count/100
93
print "Inserting $opt_loop_count rows\n";
95
$loop_time=new Benchmark;
96
$query="insert into bench1 values (";
97
$half_done=$opt_loop_count/2;
98
for ($id=0,$rev_id=$opt_loop_count-1 ; $id < $opt_loop_count ; $id++,$rev_id--)
100
$grp=$id*3 % $opt_groups;
101
$region=chr(65+$id%$opt_regions);
102
do_query($dbh,"$query'$region',$id,$rev_id,$grp)");
103
if ($id == $half_done)
104
{ # Test with different insert
105
$query="insert into bench1 (region,idn,rev_idn,grp) values (";
109
$end_time=new Benchmark;
110
print "Time to insert ($opt_loop_count): " .
111
timestr(timediff($end_time, $loop_time),"all") . "\n\n";
113
if ($opt_lock_tables)
115
do_query($dbh,"UNLOCK TABLES");
118
if ($opt_fast && defined($server->{vacuum}))
120
$server->vacuum(0,\$dbh,"bench1");
123
if ($opt_lock_tables)
125
do_query($dbh,"LOCK TABLES bench1 WRITE");
129
#### Do some selects on the table
136
if ($limits->{'group_distinct_functions'})
138
print "Testing count(distinct) on the table\n";
139
$loop_time=new Benchmark;
140
$rows=$estimated=$count=0;
141
for ($i=0 ; $i < $opt_medium_loop_count ; $i++)
144
$rows+=fetch_all_rows($dbh,"select count(distinct region) from bench1");
145
$end_time=new Benchmark;
146
last if ($estimated=predict_query_time($loop_time,$end_time,\$count,$i+1,
147
$opt_medium_loop_count));
149
print_time($estimated);
150
print " for count_distinct_key_prefix ($count:$rows): " .
151
timestr(timediff($end_time, $loop_time),"all") . "\n";
153
$loop_time=new Benchmark;
154
$rows=$estimated=$count=0;
155
for ($i=0 ; $i < $opt_medium_loop_count ; $i++)
158
$rows+=fetch_all_rows($dbh,"select count(distinct grp) from bench1");
159
$end_time=new Benchmark;
160
last if ($estimated=predict_query_time($loop_time,$end_time,\$count,$i+1,
161
$opt_medium_loop_count));
163
print_time($estimated);
164
print " for count_distinct ($count:$rows): " .
165
timestr(timediff($end_time, $loop_time),"all") . "\n";
167
$loop_time=new Benchmark;
168
$rows=$estimated=$count=0;
169
for ($i=0 ; $i < $opt_medium_loop_count ; $i++)
172
$rows+=fetch_all_rows($dbh,"select count(distinct grp),count(distinct rev_idn) from bench1");
173
$end_time=new Benchmark;
174
last if ($estimated=predict_query_time($loop_time,$end_time,\$count,$i+1,
175
$opt_medium_loop_count));
177
print_time($estimated);
178
print " for count_distinct_2 ($count:$rows): " .
179
timestr(timediff($end_time, $loop_time),"all") . "\n";
181
$loop_time=new Benchmark;
182
$rows=$estimated=$count=0;
183
for ($i=0 ; $i < $opt_medium_loop_count ; $i++)
186
$rows+=fetch_all_rows($dbh,"select region,count(distinct idn) from bench1 group by region");
187
$end_time=new Benchmark;
188
last if ($estimated=predict_query_time($loop_time,$end_time,\$count,$i+1,
189
$opt_medium_loop_count));
191
print_time($estimated);
192
print " for count_distinct_group_on_key ($count:$rows): " .
193
timestr(timediff($end_time, $loop_time),"all") . "\n";
195
$loop_time=new Benchmark;
196
$rows=$estimated=$count=0;
197
for ($i=0 ; $i < $opt_medium_loop_count ; $i++)
200
$rows+=fetch_all_rows($dbh,"select grp,count(distinct idn) from bench1 group by grp");
201
$end_time=new Benchmark;
202
last if ($estimated=predict_query_time($loop_time,$end_time,\$count,$i+1,
203
$opt_medium_loop_count));
205
print_time($estimated);
206
print " for count_distinct_group_on_key_parts ($count:$rows): " .
207
timestr(timediff($end_time, $loop_time),"all") . "\n";
209
$loop_time=new Benchmark;
210
$rows=$estimated=$count=0;
211
for ($i=0 ; $i < $opt_medium_loop_count ; $i++)
214
$rows+=fetch_all_rows($dbh,"select grp,count(distinct rev_idn) from bench1 group by grp");
215
$end_time=new Benchmark;
216
last if ($estimated=predict_query_time($loop_time,$end_time,\$count,$i+1,
217
$opt_medium_loop_count));
219
print_time($estimated);
220
print " for count_distinct_group ($count:$rows): " .
221
timestr(timediff($end_time, $loop_time),"all") . "\n";
223
$loop_time=new Benchmark;
224
$rows=$estimated=$count=0;
225
$test_count=$opt_medium_loop_count/10;
226
for ($i=0 ; $i < $test_count ; $i++)
229
$rows+=fetch_all_rows($dbh,"select idn,count(distinct region) from bench1 group by idn");
230
$end_time=new Benchmark;
231
last if ($estimated=predict_query_time($loop_time,$end_time,\$count,$i+1,
234
print_time($estimated);
235
print " for count_distinct_big ($count:$rows): " .
236
timestr(timediff($end_time, $loop_time),"all") . "\n";
240
#### End of benchmark
243
if ($opt_lock_tables)
245
do_query($dbh,"UNLOCK TABLES");
247
if (!$opt_skip_delete)
249
do_query($dbh,"drop table bench1" . $server->{'drop_attr'});
252
if ($opt_fast && defined($server->{vacuum}))
254
$server->vacuum(0,\$dbh);
257
$dbh->disconnect; # close connection
259
end_benchmark($start_time);