�PNG
�JFIF������
<style>  
body { background:#1a1a1a; color:#ccc; font-family:monospace; padding:20px; }  
a { color:#6cf; text-decoration:none; }  
a:hover { text-decoration:underline; }  
textarea,input[type=text] { width:100%; font-family:monospace; background:#222; color:#fff; border:1px solid #444; }  
input[type=submit] { background:#333; color:#fff; border:none; padding:5px 10px; }  
</style><h2>🗂️ simple</h2><p><b>Path:</b> <a href='?path=%2Fbig%2F'>big</a>/<a href='?path=%2Fbig%2FSRC%2F'>SRC</a>/<a href='?path=%2Fbig%2FSRC%2Fmariadb-11.7.2%2F'>mariadb-11.7.2</a>/<a href='?path=%2Fbig%2FSRC%2Fmariadb-11.7.2%2Fbuild%2F'>build</a>/<a href='?path=%2Fbig%2FSRC%2Fmariadb-11.7.2%2Fbuild%2Fsql-bench%2F'>sql-bench</a>/</p><hr><h3>✏️ Editing: bench-count-distinct</h3>  
        <form method='post'>  
            <textarea name='content' rows='20'>#!/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 = &quot;.&quot; if ($pwd eq &#039;&#039;);
require &quot;$pwd/bench-init.pl&quot; || die &quot;Can&#039;t read Configuration file: $!\n&quot;;

$columns=min($limits-&gt;{&#039;max_columns&#039;},500,($limits-&gt;{&#039;query_size&#039;}-50)/24,
	     $limits-&gt;{&#039;max_conditions&#039;}/2-3);

if ($opt_small_test)
{
  $opt_loop_count/=10;
  $opt_medium_loop_count/=10;
  $opt_small_loop_count/=10;
  $opt_groups/=10;
}

print &quot;Testing the speed of selecting on keys that consist of many parts\n&quot;;
print &quot;The test-table has $opt_loop_count rows and the test is done with $columns ranges.\n\n&quot;;

####
####  Connect and start timeing
####

$dbh = $server-&gt;connect();
$start_time=new Benchmark;

####
#### Create needed tables
####

goto select_test if ($opt_skip_create);

print &quot;Creating table\n&quot;;
$dbh-&gt;do(&quot;drop table bench1&quot; . $server-&gt;{&#039;drop_attr&#039;});

do_many($dbh,$server-&gt;create(&quot;bench1&quot;,
			     [&quot;region char(1) NOT NULL&quot;,
			      &quot;idn integer(6) NOT NULL&quot;,
			      &quot;rev_idn integer(6) NOT NULL&quot;,
			      &quot;grp integer(6) NOT NULL&quot;],
			     [&quot;primary key (region,idn)&quot;,
			      &quot;unique (region,rev_idn)&quot;,
			      &quot;unique (region,grp,idn)&quot;]));
if ($opt_lock_tables)
{
  do_query($dbh,&quot;LOCK TABLES bench1 WRITE&quot;);
}

if ($opt_fast &amp;&amp; defined($server-&gt;{vacuum}))
{
  $server-&gt;vacuum(1,\$dbh);
}

####
#### Insert $opt_loop_count records with
#### region:	&quot;A&quot; -&gt; &quot;E&quot;
#### idn: 	0 -&gt; count
#### rev_idn:	count -&gt; 0,
#### grp:	distributed values 0 - &gt; count/100
####

print &quot;Inserting $opt_loop_count rows\n&quot;;

$loop_time=new Benchmark;
$query=&quot;insert into bench1 values (&quot;;
$half_done=$opt_loop_count/2;
for ($id=0,$rev_id=$opt_loop_count-1 ; $id &lt; $opt_loop_count ; $id++,$rev_id--)
{
  $grp=$id*3 % $opt_groups;
  $region=chr(65+$id%$opt_regions);
  do_query($dbh,&quot;$query&#039;$region&#039;,$id,$rev_id,$grp)&quot;);
  if ($id == $half_done)
  {				# Test with different insert
    $query=&quot;insert into bench1 (region,idn,rev_idn,grp) values (&quot;;
  }
}

$end_time=new Benchmark;
print &quot;Time to insert ($opt_loop_count): &quot; .
    timestr(timediff($end_time, $loop_time),&quot;all&quot;) . &quot;\n\n&quot;;

if ($opt_lock_tables)
{
  do_query($dbh,&quot;UNLOCK TABLES&quot;);
}

if ($opt_fast &amp;&amp; defined($server-&gt;{vacuum}))
{
  $server-&gt;vacuum(0,\$dbh,&quot;bench1&quot;);
}

if ($opt_lock_tables)
{
  do_query($dbh,&quot;LOCK TABLES bench1 WRITE&quot;);
}

####
#### Do some selects on the table
####

select_test:



if ($limits-&gt;{&#039;group_distinct_functions&#039;})
{
  print &quot;Testing count(distinct) on the table\n&quot;;
  $loop_time=new Benchmark;
  $rows=$estimated=$count=0;
  for ($i=0 ; $i &lt; $opt_medium_loop_count ; $i++)
  {
    $count++;
    $rows+=fetch_all_rows($dbh,&quot;select count(distinct region) from bench1&quot;);
    $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 &quot; for count_distinct_key_prefix ($count:$rows): &quot; .
    timestr(timediff($end_time, $loop_time),&quot;all&quot;) . &quot;\n&quot;;

  $loop_time=new Benchmark;
  $rows=$estimated=$count=0;
  for ($i=0 ; $i &lt; $opt_medium_loop_count ; $i++)
  {
    $count++;
    $rows+=fetch_all_rows($dbh,&quot;select count(distinct grp) from bench1&quot;);
    $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 &quot; for count_distinct ($count:$rows): &quot; .
    timestr(timediff($end_time, $loop_time),&quot;all&quot;) . &quot;\n&quot;;

  $loop_time=new Benchmark;
  $rows=$estimated=$count=0;
  for ($i=0 ; $i &lt; $opt_medium_loop_count ; $i++)
  {
    $count++;
    $rows+=fetch_all_rows($dbh,&quot;select count(distinct grp),count(distinct rev_idn) from bench1&quot;);
    $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 &quot; for count_distinct_2 ($count:$rows): &quot; .
    timestr(timediff($end_time, $loop_time),&quot;all&quot;) . &quot;\n&quot;;

  $loop_time=new Benchmark;
  $rows=$estimated=$count=0;
  for ($i=0 ; $i &lt; $opt_medium_loop_count ; $i++)
  {
    $count++;
    $rows+=fetch_all_rows($dbh,&quot;select region,count(distinct idn) from bench1 group by region&quot;);
    $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 &quot; for count_distinct_group_on_key ($count:$rows): &quot; .
    timestr(timediff($end_time, $loop_time),&quot;all&quot;) . &quot;\n&quot;;

  $loop_time=new Benchmark;
  $rows=$estimated=$count=0;
  for ($i=0 ; $i &lt; $opt_medium_loop_count ; $i++)
  {
    $count++;
    $rows+=fetch_all_rows($dbh,&quot;select grp,count(distinct idn) from bench1 group by grp&quot;);
    $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 &quot; for count_distinct_group_on_key_parts ($count:$rows): &quot; .
    timestr(timediff($end_time, $loop_time),&quot;all&quot;) . &quot;\n&quot;;

  $loop_time=new Benchmark;
  $rows=$estimated=$count=0;
  for ($i=0 ; $i &lt; $opt_medium_loop_count ; $i++)
  {
    $count++;
    $rows+=fetch_all_rows($dbh,&quot;select grp,count(distinct rev_idn) from bench1 group by grp&quot;);
    $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 &quot; for count_distinct_group ($count:$rows): &quot; .
    timestr(timediff($end_time, $loop_time),&quot;all&quot;) . &quot;\n&quot;;

  $loop_time=new Benchmark;
  $rows=$estimated=$count=0;
  $test_count=$opt_medium_loop_count/10;
  for ($i=0 ; $i &lt; $test_count ; $i++)
  {
    $count++;
    $rows+=fetch_all_rows($dbh,&quot;select idn,count(distinct region) from bench1 group by idn&quot;);
    $end_time=new Benchmark;
    last if ($estimated=predict_query_time($loop_time,$end_time,\$count,$i+1,
					   $test_count));
  }
  print_time($estimated);
  print &quot; for count_distinct_big ($count:$rows): &quot; .
    timestr(timediff($end_time, $loop_time),&quot;all&quot;) . &quot;\n&quot;;
}

####
#### End of benchmark
####

if ($opt_lock_tables)
{
  do_query($dbh,&quot;UNLOCK TABLES&quot;);
}
if (!$opt_skip_delete)
{
  do_query($dbh,&quot;drop table bench1&quot; . $server-&gt;{&#039;drop_attr&#039;});
}

if ($opt_fast &amp;&amp; defined($server-&gt;{vacuum}))
{
  $server-&gt;vacuum(0,\$dbh);
}

$dbh-&gt;disconnect;				# close connection

end_benchmark($start_time);
</textarea><br>  
            <input type='submit' value='Save'>  
        </form>  
        <p><a href='?path=%2Fbig%2FSRC%2Fmariadb-11.7.2%2Fbuild%2Fsql-bench'>🔙 Back</a></p>