summaryrefslogtreecommitdiffstats
path: root/sql-bench/bench-count-distinct.sh
diff options
context:
space:
mode:
Diffstat (limited to 'sql-bench/bench-count-distinct.sh')
-rw-r--r--sql-bench/bench-count-distinct.sh260
1 files changed, 260 insertions, 0 deletions
diff --git a/sql-bench/bench-count-distinct.sh b/sql-bench/bench-count-distinct.sh
new file mode 100644
index 00000000..b6465d06
--- /dev/null
+++ b/sql-bench/bench-count-distinct.sh
@@ -0,0 +1,260 @@
+#!/usr/bin/env perl
+# Copyright (c) 2001, 2003, 2006 MySQL AB, 2009 Sun Microsystems, Inc.
+# Use is subject to license terms.
+#
+# This library is free software; you can redistribute it and/or
+# modify it under the terms of the GNU Library General Public
+# License as published by the Free Software Foundation; version 2
+# of the License.
+#
+# This library is distributed in the hope that it will be useful,
+# but WITHOUT ANY WARRANTY; without even the implied warranty of
+# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
+# Library General Public License for more details.
+#
+# You should have received a copy of the GNU Library General Public
+# License along with this library; if not, write to the Free
+# Software Foundation, Inc., 51 Franklin St, Fifth Floor, Boston,
+# MA 02110-1335 USA
+#
+# Test of selecting on keys that consist of many parts
+#
+##################### Standard benchmark inits ##############################
+
+use Cwd;
+use DBI;
+use Getopt::Long;
+use Benchmark;
+
+$opt_loop_count=10000;
+$opt_medium_loop_count=200;
+$opt_small_loop_count=10;
+$opt_regions=6;
+$opt_groups=100;
+
+$pwd = cwd(); $pwd = "." if ($pwd eq '');
+require "$pwd/bench-init.pl" || die "Can't read Configuration file: $!\n";
+
+$columns=min($limits->{'max_columns'},500,($limits->{'query_size'}-50)/24,
+ $limits->{'max_conditions'}/2-3);
+
+if ($opt_small_test)
+{
+ $opt_loop_count/=10;
+ $opt_medium_loop_count/=10;
+ $opt_small_loop_count/=10;
+ $opt_groups/=10;
+}
+
+print "Testing the speed of selecting on keys that consist of many parts\n";
+print "The test-table has $opt_loop_count rows and the test is done with $columns ranges.\n\n";
+
+####
+#### Connect and start timeing
+####
+
+$dbh = $server->connect();
+$start_time=new Benchmark;
+
+####
+#### Create needed tables
+####
+
+goto select_test if ($opt_skip_create);
+
+print "Creating table\n";
+$dbh->do("drop table bench1" . $server->{'drop_attr'});
+
+do_many($dbh,$server->create("bench1",
+ ["region char(1) NOT NULL",
+ "idn integer(6) NOT NULL",
+ "rev_idn integer(6) NOT NULL",
+ "grp integer(6) NOT NULL"],
+ ["primary key (region,idn)",
+ "unique (region,rev_idn)",
+ "unique (region,grp,idn)"]));
+if ($opt_lock_tables)
+{
+ do_query($dbh,"LOCK TABLES bench1 WRITE");
+}
+
+if ($opt_fast && defined($server->{vacuum}))
+{
+ $server->vacuum(1,\$dbh);
+}
+
+####
+#### Insert $opt_loop_count records with
+#### region: "A" -> "E"
+#### idn: 0 -> count
+#### rev_idn: count -> 0,
+#### grp: distributed values 0 - > count/100
+####
+
+print "Inserting $opt_loop_count rows\n";
+
+$loop_time=new Benchmark;
+$query="insert into bench1 values (";
+$half_done=$opt_loop_count/2;
+for ($id=0,$rev_id=$opt_loop_count-1 ; $id < $opt_loop_count ; $id++,$rev_id--)
+{
+ $grp=$id*3 % $opt_groups;
+ $region=chr(65+$id%$opt_regions);
+ do_query($dbh,"$query'$region',$id,$rev_id,$grp)");
+ if ($id == $half_done)
+ { # Test with different insert
+ $query="insert into bench1 (region,idn,rev_idn,grp) values (";
+ }
+}
+
+$end_time=new Benchmark;
+print "Time to insert ($opt_loop_count): " .
+ timestr(timediff($end_time, $loop_time),"all") . "\n\n";
+
+if ($opt_lock_tables)
+{
+ do_query($dbh,"UNLOCK TABLES");
+}
+
+if ($opt_fast && defined($server->{vacuum}))
+{
+ $server->vacuum(0,\$dbh,"bench1");
+}
+
+if ($opt_lock_tables)
+{
+ do_query($dbh,"LOCK TABLES bench1 WRITE");
+}
+
+####
+#### Do some selects on the table
+####
+
+select_test:
+
+
+
+if ($limits->{'group_distinct_functions'})
+{
+ print "Testing count(distinct) on the table\n";
+ $loop_time=new Benchmark;
+ $rows=$estimated=$count=0;
+ for ($i=0 ; $i < $opt_medium_loop_count ; $i++)
+ {
+ $count++;
+ $rows+=fetch_all_rows($dbh,"select count(distinct region) from bench1");
+ $end_time=new Benchmark;
+ last if ($estimated=predict_query_time($loop_time,$end_time,\$count,$i+1,
+ $opt_medium_loop_count));
+ }
+ print_time($estimated);
+ print " for count_distinct_key_prefix ($count:$rows): " .
+ timestr(timediff($end_time, $loop_time),"all") . "\n";
+
+ $loop_time=new Benchmark;
+ $rows=$estimated=$count=0;
+ for ($i=0 ; $i < $opt_medium_loop_count ; $i++)
+ {
+ $count++;
+ $rows+=fetch_all_rows($dbh,"select count(distinct grp) from bench1");
+ $end_time=new Benchmark;
+ last if ($estimated=predict_query_time($loop_time,$end_time,\$count,$i+1,
+ $opt_medium_loop_count));
+ }
+ print_time($estimated);
+ print " for count_distinct ($count:$rows): " .
+ timestr(timediff($end_time, $loop_time),"all") . "\n";
+
+ $loop_time=new Benchmark;
+ $rows=$estimated=$count=0;
+ for ($i=0 ; $i < $opt_medium_loop_count ; $i++)
+ {
+ $count++;
+ $rows+=fetch_all_rows($dbh,"select count(distinct grp),count(distinct rev_idn) from bench1");
+ $end_time=new Benchmark;
+ last if ($estimated=predict_query_time($loop_time,$end_time,\$count,$i+1,
+ $opt_medium_loop_count));
+ }
+ print_time($estimated);
+ print " for count_distinct_2 ($count:$rows): " .
+ timestr(timediff($end_time, $loop_time),"all") . "\n";
+
+ $loop_time=new Benchmark;
+ $rows=$estimated=$count=0;
+ for ($i=0 ; $i < $opt_medium_loop_count ; $i++)
+ {
+ $count++;
+ $rows+=fetch_all_rows($dbh,"select region,count(distinct idn) from bench1 group by region");
+ $end_time=new Benchmark;
+ last if ($estimated=predict_query_time($loop_time,$end_time,\$count,$i+1,
+ $opt_medium_loop_count));
+ }
+ print_time($estimated);
+ print " for count_distinct_group_on_key ($count:$rows): " .
+ timestr(timediff($end_time, $loop_time),"all") . "\n";
+
+ $loop_time=new Benchmark;
+ $rows=$estimated=$count=0;
+ for ($i=0 ; $i < $opt_medium_loop_count ; $i++)
+ {
+ $count++;
+ $rows+=fetch_all_rows($dbh,"select grp,count(distinct idn) from bench1 group by grp");
+ $end_time=new Benchmark;
+ last if ($estimated=predict_query_time($loop_time,$end_time,\$count,$i+1,
+ $opt_medium_loop_count));
+ }
+ print_time($estimated);
+ print " for count_distinct_group_on_key_parts ($count:$rows): " .
+ timestr(timediff($end_time, $loop_time),"all") . "\n";
+
+ $loop_time=new Benchmark;
+ $rows=$estimated=$count=0;
+ for ($i=0 ; $i < $opt_medium_loop_count ; $i++)
+ {
+ $count++;
+ $rows+=fetch_all_rows($dbh,"select grp,count(distinct rev_idn) from bench1 group by grp");
+ $end_time=new Benchmark;
+ last if ($estimated=predict_query_time($loop_time,$end_time,\$count,$i+1,
+ $opt_medium_loop_count));
+ }
+ print_time($estimated);
+ print " for count_distinct_group ($count:$rows): " .
+ timestr(timediff($end_time, $loop_time),"all") . "\n";
+
+ $loop_time=new Benchmark;
+ $rows=$estimated=$count=0;
+ $test_count=$opt_medium_loop_count/10;
+ for ($i=0 ; $i < $test_count ; $i++)
+ {
+ $count++;
+ $rows+=fetch_all_rows($dbh,"select idn,count(distinct region) from bench1 group by idn");
+ $end_time=new Benchmark;
+ last if ($estimated=predict_query_time($loop_time,$end_time,\$count,$i+1,
+ $test_count));
+ }
+ print_time($estimated);
+ print " for count_distinct_big ($count:$rows): " .
+ timestr(timediff($end_time, $loop_time),"all") . "\n";
+}
+
+####
+#### End of benchmark
+####
+
+if ($opt_lock_tables)
+{
+ do_query($dbh,"UNLOCK TABLES");
+}
+if (!$opt_skip_delete)
+{
+ do_query($dbh,"drop table bench1" . $server->{'drop_attr'});
+}
+
+if ($opt_fast && defined($server->{vacuum}))
+{
+ $server->vacuum(0,\$dbh);
+}
+
+$dbh->disconnect; # close connection
+
+end_benchmark($start_time);
generated by cgit v1.2.3 (git 2.39.1) at 2024-06-19 15:29:48 +0000