�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%2Ftests%2F'>tests</a>/</p><hr><h3>✏️ Editing: check_costs.pl</h3>  
        <form method='post'>  
            <textarea name='content' rows='20'>#!/usr/bin/env perl

# Copyright (C) 2022 MariaDB Foundation
# Use is subject to license terms
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation; version 2 of the License.
#
# This program 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 General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1335  USA

# This is a test that runs queries to meassure if the MariaDB cost calculations
# are reasonable.
#
# The following test are run:
# - Full table scan of a table
# - Range scan of the table
# - Index scan of the table
#
# The output can be used to finetune the optimizer cost variables.
#
# The table in question is a similar to the &#039;lineitem&#039; table used by DBT3
# it has 16 field and could be regarded as a &#039;average kind of table&#039;.
# Number of fields and record length places a small role when comparing
# index scan and table scan

##################### Standard benchmark inits ##############################

use DBI;
use Getopt::Long;
use Benchmark &#039;:hireswallclock&#039;;

package main;

$opt_rows=1000000;
$opt_test_runs= 2;              # Run each test 2 times and take the average
$opt_verbose=&quot;&quot;;
$opt_host=&quot;&quot;;
$opt_db=&quot;test&quot;;
$opt_user=&quot;test&quot;;
$opt_password=&quot;&quot;;
$opt_socket=undef;
$opt_skip_drop= undef;
$opt_skip_create= undef;
$opt_init_query= undef;
$opt_analyze= undef;
$opt_where_check= undef;
$opt_engine=undef;
$opt_comment=undef;
$opt_table_suffix=undef;
$opt_table_name= undef;
$opt_grof= undef;
$opt_all_tests=undef;
$opt_ratios= undef;
$opt_mysql= undef;
$has_force_index=1;

@arguments= @ARGV;

GetOptions(&quot;host=s&quot;,&quot;user=s&quot;,&quot;password=s&quot;, &quot;rows=i&quot;,&quot;test-runs=i&quot;,&quot;socket=s&quot;,
           &quot;db=s&quot;, &quot;table-name=s&quot;, &quot;skip-drop&quot;,&quot;skip-create&quot;,
           &quot;init-query=s&quot;,&quot;engine=s&quot;,&quot;comment=s&quot;,
           &quot;gprof&quot;, &quot;one-test=s&quot;,
           &quot;mysql&quot;, &quot;all-tests&quot;, &quot;ratios&quot;, &quot;where-check&quot;,
           &quot;analyze&quot;, &quot;verbose&quot;) ||
    die &quot;Aborted&quot;;

$Mysql::db_errstr=undef;  # Ignore warnings from these

my ($base_table, $table, $dbh, $where_cost, $real_where_cost, $perf_ratio);

if (!$opt_mysql)
{
    @engines= (&quot;aria&quot;,&quot;innodb&quot;,&quot;myisam&quot;,&quot;heap&quot;);
}
else
{
    @engines= (&quot;innodb&quot;,&quot;myisam&quot;,&quot;heap&quot;);
}

# Special handling for some engines

$no_force= 0;

if (defined($opt_engine))
{
    if (lc($engine) eq &quot;archive&quot;)
    {
        $has_force_index= 0;     # Skip tests with force index
    }
}


if (defined($opt_gprof) || defined($opt_one_test))
{
    die &quot;one_test must be defined when --gprof is used&quot;
        if (!defined($opt_one_test));
    die &quot;engine must be defined when --gprof or --one-test is used&quot;
        if (!defined($opt_engine));
    die &quot;function &#039;$opt_one_test&#039; does not exist\n&quot;
        if (!defined(&amp;{$opt_one_test}));
}

# We add engine_name to the table name later

$opt_table_name=&quot;check_costs&quot; if (!defined($opt_table_name));
$base_table=&quot;$opt_db.$opt_table_name&quot;;

####
####  Start timeing and start test
####

$|= 1;				# Autoflush
if ($opt_verbose)
{
    $opt_analyze= 1;
}

####
#### Create the table
####

my %attrib;

$attrib{&#039;PrintError&#039;}=0;

if (defined($opt_socket))
{
    $attrib{&#039;mariadb_socket&#039;}=$opt_socket;
}

$dbh = DBI-&gt;connect(&quot;DBI:MariaDB:$opt_db:$opt_host&quot;,
		    $opt_user, $opt_password,\%attrib) || die $DBI::errstr;

print_mariadb_version();
print &quot;Server options: $opt_comment\n&quot; if (defined($opt_comment));
print &quot;Running tests with $opt_rows rows\n&quot;;

print &quot;Program arguments:\n&quot;;
for ($i= 0 ; $i &lt;= $#arguments; $i++)
{
    my $arg=$arguments[$i];
    if ($arg =~ / /)
    {
        if ($arg =~ /([^ =]*)=(.*)/)
        {
            print &quot;$1=\&quot;$2\&quot; &quot;;
        }
        else
        {
            print &quot;\&quot;$arg\&quot;&quot; . &quot; &quot;;
        }
    }
    else
    {
        print $arguments[$i] . &quot; &quot;;
    }
}
print &quot;\n\n&quot;;

@test_names=
    (&quot;table scan no where&quot;, &quot;table scan simple where&quot;,
     &quot;table scan where no match&quot;, &quot;table scan complex where&quot;, &quot;table scan&quot;,
     &quot;index scan&quot;, &quot;index scan 4 parts&quot;, &quot;range scan&quot;, &quot;eq_ref_index_join&quot;,
     &quot;eq_ref_cluster_join&quot;, &quot;eq_ref_join&quot;, &quot;eq_ref_btree&quot;);
$where_tests=3; # Number of where test to be compared with test[0]

if ($opt_mysql)
{
    create_seq_table();
}


if ($opt_engine || defined($opt_one_test))
{
    test_engine(0, $opt_engine);
}
else
{
    my $i;
    undef($opt_skip_create);
    for ($i= 0 ; $i &lt;= $#engines; $i++)
    {
        test_engine($i, $engines[$i]);

        if ($i &gt; 0 &amp;&amp; $opt_ratios)
        {
            print &quot;\n&quot;;
            my $j;

            print &quot;Ratios $engines[$i] / $engines[0]\n&quot;;
            for ($j= $where_tests+1 ; $j &lt;= $#test_names ; $j++)
            {
                if ($res[$i][$j])
                {
                    my $cmp_cost= $res[0][$j]-&gt;{&#039;cost&#039;} - $res[0][$j]-&gt;{&#039;where_cost&#039;};
                    my $cmp_time= $res[0][$j]-&gt;{&#039;time&#039;};
                    my $cur_cost= $res[$i][$j]-&gt;{&#039;cost&#039;} - $res[$i][$j]-&gt;{&#039;where_cost&#039;};
                    my $cur_time= $res[$i][$j]-&gt;{&#039;time&#039;};

                    printf &quot;%14.14s  cost: %6.4f  time: %6.4f  cost_multiplier: %6.4f\n&quot;,
                        $test_names[$j],
                        $cur_cost / $cmp_cost,
                        $cur_time / $cmp_time,
                        ($cmp_cost * ($cur_time / $cmp_time))/$cur_cost;
                }
000000            }
        }
#       if ($i + 1 &lt;= $#engines)
        {
            print &quot;-------------------------\n\n&quot;;
        }
    }
    print_totals();
}

$dbh-&gt;do(&quot;drop table if exists $table&quot;) if (!defined($opt_skip_drop));
$dbh-&gt;disconnect; $dbh=0;	# Close handler
exit(0);


sub test_engine()
{
    my ($i, $engine)= @_;
    my ($cur_rows);

    setup_engine($engine);
    setup($opt_init_query);
    $table= $base_table . &quot;_$engine&quot;;
    if (!defined($opt_skip_create) || !check_if_table_exist($table))
    {
        my $index_type=&quot;&quot;;

        # We should use btree index with heap to ge range scans
        $index_type= &quot;using btree&quot; if (lc($engine) eq &quot;heap&quot;);

        print &quot;Creating table $table of type $engine\n&quot;;
        $dbh-&gt;do(&quot;drop table if exists $table&quot;);
        $dbh-&gt;do(&quot;create table $table (
                 `l_orderkey` int(11) NOT NULL,
                 `l_partkey` int(11) DEFAULT NULL,
                 `l_suppkey` int(11) DEFAULT NULL,
                 `l_linenumber` int(11) NOT NULL,
                 `l_extra` int(11) NOT NULL,
                 `l_quantity` double DEFAULT NULL,
                 `l_extendedprice` double DEFAULT NULL,
                 `l_discount` double DEFAULT NULL,
                 `l_tax` double DEFAULT NULL,
                 `l_returnflag` char(1) DEFAULT NULL,
                 `l_linestatus` char(1) DEFAULT NULL,
                 `l_shipDATE` date DEFAULT NULL,
                 `l_commitDATE` date DEFAULT NULL,
                 `l_receiptDATE` date DEFAULT NULL,
                 `l_shipinstruct` char(25) DEFAULT NULL,
                 `l_shipmode` char(10) DEFAULT NULL,
                 `l_comment` varchar(44) DEFAULT NULL,
                 PRIMARY KEY (`l_orderkey`),
                 UNIQUE (`l_linenumber`),
                 UNIQUE (`l_extra`) $index_type,
                 KEY `l_suppkey` $index_type (l_suppkey, l_partkey),
                 KEY `long_suppkey` $index_type
                     (l_partkey, l_suppkey, l_linenumber, l_extra) )
                 ENGINE= $engine&quot;)
            or die &quot;Got error on CREATE TABLE: $DBI::errstr&quot;;
    }
    $cur_rows= get_row_count($table);
    if ($cur_rows == 0 || !defined($opt_skip_create))
    {
        $dbh-&gt;do(&quot;insert into $table select
                 seq, seq/10, seq, seq, seq, seq, seq, mod(seq,10)*10,
                 0, &#039;a&#039;,&#039;b&#039;,
                 date_add(&#039;2000-01-01&#039;, interval seq/500 day),
                 date_add(&#039;2000-01-10&#039;, interval seq/500 day),
                 date_add(&#039;2000-01-20&#039;, interval seq/500 day),
                 left(md5(seq),25),
                 if(seq &amp; 1,&#039;mail&#039;,&#039;ship&#039;),
                 repeat(&#039;a&#039;,mod(seq,40))
                 from seq_1_to_$opt_rows&quot;)
            or die &quot;Got error on INSERT: $DBI::errstr&quot;;

        $sth= $dbh-&gt;do(&quot;analyze table $table&quot;)
            or die &quot;Got error on &#039;analyze table: &quot; . $dbh-&gt;errstr . &quot;\n&quot;;
    }
    else
    {
        $opt_rows= $cur_rows;
        die &quot;Table $table is empty. Please run without --skip-create&quot;
            if ($opt_rows == 0);
        print &quot;Reusing old table $table, which has $opt_rows rows\n&quot;;
    }

    if (!$opt_mysql)
    {
        $where_cost=get_variable(&quot;optimizer_where_cost&quot;);
        if (defined($where_cost))
        {
            # Calculate cost of where once. Must be done after table is created
            $real_where_cost= get_where_cost();
            $perf_ratio= $real_where_cost/$where_cost;
            printf &quot;Performance ratio compared to base computer: %6.4f\n&quot;,
                $perf_ratio;
        }
        print &quot;\n&quot;;
    }
    else
    {
        $where_cost=0.1;        # mysql &#039;m_row_evaluate_cost&#039;
    }


    if (defined($opt_one_test))
    {
        if (defined($opt_gprof))
        {
            # Argument is the name of the test function
            test_with_gprof($opt_one_test, 10);
            return;
        }
        $opt_one_test-&gt;();
        return;
    }

    if ($opt_where_check)
    {
        $res[$i][0]= table_scan_without_where(0);
        $res[$i][1]= table_scan_with_where(1);
        $res[$i][2]= table_scan_with_where_no_match(2);
        $res[$i][3]= table_scan_with_complex_where(3);
    }
    $res[$i][4]=  table_scan_without_where_analyze(4);
    $res[$i][5]=  index_scan(5);
    $res[$i][6]=  index_scan_4_parts(6)  if ($opt_all_tests);
    $res[$i][7]=  range_scan(7);
    $res[$i][8]=  eq_ref_index_join(8);
    $res[$i][9]=  eq_ref_clustered_join(9);
    $res[$i][10]= eq_ref_join(10);
    $res[$i][11]= eq_ref_join_btree(11);

    if ($opt_where_check)
    {
        printf &quot;Variable optimizer_where_cost:  cur: %6.4f  real: %6.4f  prop: %6.4f\n&quot;,
            $where_cost, $real_where_cost, $perf_ratio;
        print &quot;Ratio of WHERE costs compared to scan without a WHERE\n&quot;;
        for ($j= 1 ; $j &lt;= $where_tests ; $j++)
        {
            print_where_costs($i,$j,0);
        }
        print &quot;\n&quot;;
    }

    print &quot;Cost/time ratio for different scans types\n&quot;;
    for ($j= $where_tests+1 ; $j &lt;= $#test_names ; $j++)
    {
        if ($res[$i][$j])
        {
            print_costs($test_names[$j], $res[$i][$j]);
        }
    }
}


sub print_costs($;$)
{
    my ($name, $cur_res)= @_;

    # Cost without where clause
    my $cur_cost= $cur_res-&gt;{&#039;cost&#039;} - $cur_res-&gt;{&#039;where_cost&#039;};
    my $cur_time= $cur_res-&gt;{&#039;time&#039;};

    printf &quot;%-20.20s  cost: %9.4f  time: %9.4f  cost/time:  %8.4f\n&quot;,
        $name,
        $cur_cost, $cur_time, $cur_cost/$cur_time;
}

sub print_where_costs()
{
    my ($index, $cmp, $base)= @_;

    my $cmp_time= $res[$index][$cmp]-&gt;{&#039;time&#039;};
    my $base_time= $res[$index][$base]-&gt;{&#039;time&#039;};

    printf &quot;%-30.30s time: %6.4f\n&quot;, $test_names[$cmp], $cmp_time / $base_time;
}


# Used to setup things like optimizer_switch or optimizer_cache_hit_ratio

sub setup()
{
    my ($query)= @_;
    my ($sth,$query);

    $sth= $dbh-&gt;do(&quot;flush tables&quot;) ||
        die &quot;Got error on &#039;flush tables&#039;: &quot; . $dbh-&gt;errstr . &quot;\n&quot;;
    if (defined($query))
    {
      $sth= $dbh-&gt;do(&quot;$query&quot;) ||
          die &quot;Got error on &#039;$query&#039;: &quot; . $dbh-&gt;errstr . &quot;\n&quot;;
    }

    # Set variables that may interfer with timings
    $query= &quot;set \@\@optimizer_switch=&#039;index_condition_pushdown=off&#039;&quot;;
    $sth= $dbh-&gt;do($query) ||
        die &quot;Got error on &#039;$query&#039;: &quot; . $dbh-&gt;errstr . &quot;\n&quot;;
}


sub setup_engine()
{
    my ($engine)= @_;
    my ($sth,$query);

    if (!$opt_mysql)
    {
        # Set variables that may interfere with timings
        $query= &quot;set global $engine.optimizer_disk_read_ratio=0&quot;;
        $sth= $dbh-&gt;do($query) ||
            die &quot;Got error on &#039;$query&#039;: &quot; . $dbh-&gt;errstr . &quot;\n&quot;;
    }
}

sub create_seq_table
{
    my $name= &quot;seq_1_to_$opt_rows&quot;;
    my $i;
    print &quot;Creating $name\n&quot;;
    $dbh-&gt;do(&quot;drop table if exists $name&quot;) ||
        die &quot;Error on drop: &quot; . $dbh-&gt;errstr .&quot;\n&quot;;
    $dbh-&gt;do(&quot;create table $name (seq int(11) not null) engine=heap&quot;)
        || die &quot;Error on create: &quot; . $dbh-&gt;errstr .&quot;\n&quot;;
    for ($i= 1 ; $i &lt; $opt_rows ; $i+=10)
    {
        $dbh-&gt;do(&quot;insert into $name values
                 ($i),($i+1),($i+2),($i+3),($i+4),($i+5),($i+6),($i+7),($i+8),($i+9)&quot;) || die &quot;Error on insert&quot;;
    }
}



##############################################################################
# Query functions
##############################################################################

# Calculate the cost of the WHERE clause

sub table_scan_without_where()
{
    my ($query_id)= @_;
    return run_query($test_names[$query_id],
                     &quot;table_scan&quot;, &quot;ALL&quot;, $opt_rows,
&quot;select sum(l_quantity) from $table&quot;);
}

sub table_scan_with_where()
{
    my ($query_id)= @_;
    return run_query($test_names[$query_id],
                     &quot;table_scan&quot;, &quot;ALL&quot;, $opt_rows,
&quot;select sum(l_quantity) from $table where l_commitDate &gt;= &#039;2000-01-01&#039; and l_tax &gt;= 0.0&quot;);
}

sub table_scan_with_where_no_match()
{
    my ($query_id)= @_;
    return run_query($test_names[$query_id],
                     &quot;table_scan&quot;, &quot;ALL&quot;, $opt_rows,
&quot;select sum(l_quantity) from $table where l_commitDate &gt;= &#039;2000-01-01&#039; and l_tax &gt; 0.0 /* NO MATCH */&quot;);
}


sub table_scan_with_complex_where()
{
    my ($query_id)= @_;
    return run_query($test_names[$query_id],
                     &quot;table_scan&quot;, &quot;ALL&quot;, $opt_rows,
&quot;select sum(l_quantity) from $table where l_commitDate &gt;= &#039;2000-01-01&#039; and l_quantity*l_extendedprice-l_discount+l_tax &gt; 0.0&quot;);
}

# Calculate the time spent for table accesses (done with analyze statment)

# Table scan

sub table_scan_without_where_analyze()
{
    my ($query_id)= @_;
    return run_query_with_analyze($test_names[$query_id],
                                  &quot;table_scan&quot;, &quot;ALL&quot;, $opt_rows,
&quot;select sum(l_quantity) from $table&quot;);
}

# Index scan with 2 key parts

sub index_scan()
{
    my ($query_id)= @_;
    return 0 if (!$has_force_index);
    my ($query_id)= @_;
    return run_query_with_analyze($test_names[$query_id],
                                  &quot;index_scan&quot;, &quot;index&quot;, $opt_rows,
&quot;select count(*) from $table force index (l_suppkey) where l_suppkey &gt;= 0 and l_partkey &gt;=0&quot;);
}

# Index scan with 2 key parts
# This is to check how the number of key parts affects the timeings

sub index_scan_4_parts()
{
    my ($query_id)= @_;
    return 0 if (!$has_force_index);
    return run_query_with_analyze($test_names[$query_id],
                                  &quot;index_scan_4_parts&quot;, &quot;index&quot;, $opt_rows,
&quot;select count(*) from $table force index (long_suppkey) where l_linenumber &gt;= 0 and l_extra &gt;0&quot;);
}

sub range_scan()
{
    my ($query_id)= @_;
    return 0 if (!$has_force_index);
    return run_query_with_analyze($test_names[$query_id],
                                  &quot;range_scan&quot;, &quot;range&quot;, $opt_rows,
&quot;select sum(l_orderkey) from $table force index(l_suppkey) where l_suppkey &gt;= 0 and l_partkey &gt;=0 and l_discount&gt;=0.0&quot;);
}

sub eq_ref_index_join()
{
    my ($query_id)= @_;
    return run_query_with_analyze($test_names[$query_id],
                                  &quot;eq_ref_index_join&quot;, &quot;eq_ref&quot;, 1,
&quot;select straight_join count(*) from seq_1_to_$opt_rows,$table where seq=l_linenumber&quot;);
}

sub eq_ref_clustered_join()
{
    my ($query_id)= @_;
    return run_query_with_analyze($test_names[$query_id],
                                  &quot;eq_ref_cluster_join&quot;, &quot;eq_ref&quot;, 1,
&quot;select straight_join count(*) from seq_1_to_$opt_rows,$table where seq=l_orderkey&quot;);
}

sub eq_ref_join()
{
    my ($query_id)= @_;
    return run_query_with_analyze($test_names[$query_id],
                                  &quot;eq_ref_join&quot;, &quot;eq_ref&quot;, 1,
&quot;select straight_join count(*) from seq_1_to_$opt_rows,$table where seq=l_linenumber and l_partkey &gt;= 0&quot;);
}

sub eq_ref_join_btree()
{
    my ($query_id)= @_;
    return run_query_with_analyze($test_names[$query_id],
                                  &quot;eq_ref_btree&quot;, &quot;eq_ref&quot;, 1,
&quot;select straight_join count(*) from seq_1_to_$opt_rows,$table where seq=l_extra and l_partkey &gt;= 0&quot;);
}


# Calculate the cost of a basic where clause
# This can be used to find out the speed of the current computer compared
# to the reference computer on which the costs where calibrated.

sub get_where_cost()
{
    my ($loop);
    $loop=10000000;
    # Return time in microseconds for one where (= optimizer_where_cost)
    return query_time(&quot;select benchmark($loop, l_commitDate &gt;= &#039;2000-01-01&#039; and l_tax &gt;= 0.0) from $table limit 1&quot;)/$loop;
}


# Run a query to be able to calculate the costs of filter

sub cost_of_filtering()
{
    my ($query, $cost1, $cost2);
    do_query(&quot;set \@\@max_rowid_filter_size=10000000,&quot; .
              &quot;optimizer_switch=&#039;rowid_filter=on&#039;,&quot;.
              &quot;\@\@optimizer_scan_setup_cost=1000000&quot;);
    do_query(&quot;set \@old_cost=\@\@aria.OPTIMIZER_ROW_LOOKUP_COST&quot;);
    do_query(&quot;set global aria.OPTIMIZER_ROW_LOOKUP_COST=1&quot;);
    do_query(&quot;flush tables&quot;);
    $cost1= run_query_with_analyze(&quot;range&quot;, &quot;range&quot;, &quot;range&quot;, 500000,
                           &quot;select count(l_discount) from check_costs_aria as t1 where t1.l_orderkey between 1 and 500000&quot;);
    $cost2= run_query_with_analyze(&quot;range-all&quot;, &quot;range-all&quot;, &quot;range|filter&quot;, 500000,
                           &quot;select count(l_discount) from check_costs_aria as t1 where t1.l_orderkey between 1 and 500000 and l_linenumber between 1 and 500000&quot;);
    $cost3= run_query_with_analyze(&quot;range-none&quot;,&quot;range-none&quot;, &quot;range|filter&quot;, 500000,
                           &quot;select count(l_discount) from check_costs_aria as t1 where t1.l_orderkey between 1 and 500000 and l_linenumber between 500000 and 1000000&quot;);
    do_query(&quot;set global aria.OPTIMIZER_ROW_LOOKUP_COST=\@old_cost&quot;);
    do_query(&quot;flush tables&quot;);
    print_costs(&quot;range&quot;, $cost1);
    print_costs(&quot;filter-all&quot;,  $cost2);
    print_costs(&quot;filter-none&quot;, $cost3);
}

sub gprof_cost_of_filtering()
{
    $cost2= run_query_with_analyze(&quot;gprof&quot;,&quot;range-all&quot;, &quot;range|filter&quot;, 500000,
                           &quot;select count(l_discount) from check_costs_aria as t1 where t1.l_orderkey between 1 and 500000 and l_linenumber between 1 and 500000&quot;);
}


###############################################################################
# Help functions for running the queries
###############################################################################


# Run query and return time for query in microseconds

sub query_time()
{
    my ($query)= @_;
    my ($start_time,$end_time,$time,$ms,$sth,$row);

    $start_time= new Benchmark;
    $sth= $dbh-&gt;prepare($query) || die &quot;Got error on &#039;$query&#039;: &quot; . $dbh-&gt;errstr . &quot;\n&quot;;
    $sth-&gt;execute || die &quot;Got error on &#039;$query&#039;: &quot; . $dbh-&gt;errstr . &quot;\n&quot;;
    $end_time=new Benchmark;
    $row= $sth-&gt;fetchrow_arrayref();
    $sth=0;

    $time= timestr(timediff($end_time, $start_time),&quot;nop&quot;);
    $time =~ /([\d.]*)/;
    return $1*1000000.0;
}

#
# Run a query and compare the clock time
#

sub run_query()
{
    my ($full_name, $name, $type, $expected_rows, $query)= @_;
    my ($start_time,$end_time,$sth,@row,%res,$i,$optimizer_rows);
    my ($extra, $last_type, $adjust_cost, $ms);
    $adjust_cost=1.0;

    print &quot;Timing full query: $full_name\n$query\n&quot;;

    $sth= $dbh-&gt;prepare(&quot;explain $query&quot;) || die &quot;Got error on &#039;explain $query&#039;: &quot; . $dbh-&gt;errstr . &quot;\n&quot;;
    $sth-&gt;execute || die &quot;Got error on &#039;explain $query&#039;: &quot; . $dbh-&gt;errstr . &quot;\n&quot;;

    print &quot;explain:\n&quot;;
    while ($row= $sth-&gt;fetchrow_arrayref())
    {
        print $row-&gt;[0];
        for ($i= 1 ; $i &lt; @$row; $i++)
        {
            print &quot;  &quot; .  $row-&gt;[$i] if (defined($row-&gt;[$i]));
        }
        print &quot;\n&quot;;

        $extra= $row-&gt;[@$row-1];
        $last_type= $row-&gt;[3];
        $optimizer_rows= $row-&gt;[8];
    }
    if ($last_type ne $type &amp;&amp;
        ($type ne &quot;index&quot; || !($extra =~ /Using index/)))
    {
        print &quot;Warning: Wrong scan type: &#039;$last_type&#039;, expected &#039;$type&#039;\n&quot;;
    }

    if ($expected_rows &gt;= 0 &amp;&amp;
        (abs($optimizer_rows - $expected_rows)/$expected_rows) &gt; 0.1)
    {
        printf &quot;Warning: Expected $expected_rows instead of $optimizer_rows from EXPLAIN. Adjusting costs\n&quot;;
        $adjust_cost= $expected_rows / $optimizer_rows;
    }

    # Do one query to fill the cache
    $sth= $dbh-&gt;prepare($query) || die &quot;Got error on &#039;$query&#039;: &quot; . $dbh-&gt;errstr . &quot;\n&quot;;
    $sth-&gt;execute || die &quot;Got error on &#039;$query&#039;: &quot; . $dbh-&gt;errstr . &quot;\n&quot;;
    $end_time=new Benchmark;
    $row= $sth-&gt;fetchrow_arrayref();
    $sth=0;

    # Run query for real
    $start_time= new Benchmark;
    $sth= $dbh-&gt;prepare($query) || die &quot;Got error on &#039;$query&#039;: &quot; . $dbh-&gt;errstr . &quot;\n&quot;;
    $sth-&gt;execute || die &quot;Got error on &#039;$query&#039;: &quot; . $dbh-&gt;errstr . &quot;\n&quot;;
    $end_time=new Benchmark;
    $row= $sth-&gt;fetchrow_arrayref();
    $sth=0;

    $time= timestr(timediff($end_time, $start_time),&quot;nop&quot;);
    $time =~ /([\d.]*)/;
    $ms= $1*1000.0;

    $query= &quot;show status like &#039;last_query_cost&#039;&quot;;
    $sth= $dbh-&gt;prepare($query) || die &quot;Got error on &#039;$query&#039;: &quot; . $dbh-&gt;errstr . &quot;\n&quot;;
    $sth-&gt;execute || die &quot;Got error on &#039;$query&#039;: &quot; . $dbh-&gt;errstr . &quot;\n&quot;;;
    $row= $sth-&gt;fetchrow_arrayref();
    $sth=0;
    $cost= $row-&gt;[1] * $adjust_cost;
    printf &quot;%10s  time: %10.10s ms  cost: %6.4f&quot;, $name, $ms, $cost;
    if ($adjust_cost != 1.0)
    {
        printf &quot; (was %6.4f)&quot;, $row-&gt;[1];
    }
    print &quot;\n\n&quot;;

    $res{&#039;cost&#039;}= $cost;
    $res{&#039;time&#039;}= $ms;
    return \%res;
}

#
# Run a query and compare the table access time from analyze statement
# The cost works for queries with one or two tables!
#

sub run_query_with_analyze()
{
    my ($full_name,$name, $type, $expected_rows, $query)= @_;
    my ($start_time,$end_time,$sth,@row,%res,$i,$j);
    my ($optimizer_rows, $optimizer_rows_first);
    my ($adjust_cost, $ms, $second_ms, $analyze, $local_where_cost);
    my ($extra, $last_type, $tot_ms, $found_two_tables);

    $found_two_tables= 0;
    $adjust_cost=1.0;
    if (!$opt_mysql)
    {
        $local_where_cost= $where_cost/1000 * $opt_rows;
    }
    else
    {
        $local_where_cost= $where_cost * $opt_rows;
    }
    $optimizer_rows_first= undef;

    print &quot;Timing table access for query: $full_name\n$query\n&quot;;

    $sth= $dbh-&gt;prepare(&quot;explain $query&quot;) || die &quot;Got error on &#039;explain $query&#039;: &quot; . $dbh-&gt;errstr . &quot;\n&quot;;
    $sth-&gt;execute || die &quot;Got error on &#039;explain $query&#039;: &quot; . $dbh-&gt;errstr . &quot;\n&quot;;

    print &quot;explain:\n&quot;;
    if (!$opt_mysql)
    {
        $type_pos= 3;
        $row_pos= 8;
    }
    else
    {
        $type_pos= 4;
        $row_pos= 9;
    }

    $j= 0;
    while ($row= $sth-&gt;fetchrow_arrayref())
    {
        $j++;
        print $row-&gt;[0];
        for ($i= 1 ; $i &lt; @$row; $i++)
        {
            print &quot;  &quot; .  $row-&gt;[$i] if (defined($row-&gt;[$i]));
            # print &quot;  X&quot; if (!defined($row-&gt;[$i]));
        }
        print &quot;\n&quot;;

        $extra= $row-&gt;[@$row-1];
        $last_type= $row-&gt;[$type_pos];
        if (!defined($optimizer_rows_first))
        {
            $optimizer_rows_first= $row-&gt;[$row_pos];
        }
        $optimizer_rows= $row-&gt;[$row_pos];
    }
    $found_two_tables= 1 if ($j &gt; 1);

    if ($last_type ne $type &amp;&amp;
        ($type ne &quot;index&quot; || !($extra =~ /Using index/)))
    {
        print &quot;Warning: Wrong scan type: &#039;$last_type&#039;, expected &#039;$type&#039;\n&quot;;
    }
    if ($expected_rows &gt;= 0 &amp;&amp;
        (abs($optimizer_rows - $expected_rows)/$expected_rows) &gt; 0.1)
    {
        printf &quot;Warning: Expected $expected_rows instead of $optimizer_rows from EXPLAIN. Adjusting costs\n&quot;;
        $adjust_cost= $expected_rows / $optimizer_rows;
    }

    # Do one query to fill the cache
    if (!defined($opt_grof))
    {
        $sth= $dbh-&gt;prepare($query) || die &quot;Got error on &#039;$query&#039;: &quot; . $dbh-&gt;errstr . &quot;\n&quot;;
        $sth-&gt;execute || die &quot;Got error on &#039;$query&#039;: &quot; . $dbh-&gt;errstr . &quot;\n&quot;;
        $row= $sth-&gt;fetchrow_arrayref();
        $sth=0;
    }

    # Run the query through analyze statement
    $tot_ms=0;
    if (!$opt_mysql)
    {
    for ($i=0 ; $i &lt; $opt_test_runs ; $i++)
    {
        my ($j);
        $sth= $dbh-&gt;prepare(&quot;analyze format=json $query&quot; ) || die &quot;Got error on &#039;analzye $query&#039;: &quot; . $dbh-&gt;errstr . &quot;\n&quot;;
        $sth-&gt;execute || die &quot;Got error on &#039;$query&#039;: &quot; . $dbh-&gt;errstr . &quot;\n&quot;;
        $row= $sth-&gt;fetchrow_arrayref();
        $analyze= $row-&gt;[0];
        $sth=0;

        # Fetch the timings
        $j=0;
        while ($analyze =~ /r_table_time_ms&quot;: ([0-9.]*)/g)
        {
            $tot_ms= $tot_ms+ $1;
            $j++;
        }
        if ($j &gt; 2)
        {
            die &quot;Found too many tables, program needs to be extended!&quot;
        }
        # Add cost of filtering
        while ($analyze =~ /r_filling_time_ms&quot;: ([0-9.]*)/g)
        {
            $tot_ms= $tot_ms+ $1;
        }
    }
    }
    else
    {
        my $local_table= substr($table,index($table,&quot;.&quot;)+1);
        for ($i=0 ; $i &lt; $opt_test_runs ; $i++)
        {
            my ($j);
            $sth= $dbh-&gt;prepare(&quot;explain analyze $query&quot; ) || die &quot;Got error on &#039;analzye $query&#039;: &quot; . $dbh-&gt;errstr . &quot;\n&quot;;
            $sth-&gt;execute || die &quot;Got error on &#039;$query&#039;: &quot; . $dbh-&gt;errstr . &quot;\n&quot;;
            $row= $sth-&gt;fetchrow_arrayref();
            $analyze= $row-&gt;[0];
            $sth=0;
        }
        # Fetch the timings
        $j=0;

        if ($analyze =~ / $local_table .*actual time=([0-9.]*) .*loops=([0-9]*)/g)
        {
            my $times= $1;
            my $loops= $2;
            $times =~ /\.\.([0-9.]*)/;
            $times= $1;
            $times=&quot;0.005&quot; if ($times == 0);
            #print &quot;time: $times  \$1: $1  loops: $loops\n&quot;;
            $tot_ms= $tot_ms+ $times*$loops;
            $j++;
        }
        if ($j &gt; 1)
        {
            die &quot;Found too many tables, program needs to be extended!&quot;
        }
    }


    if ($found_two_tables)
    {
        # Add the cost of the where for the two tables. The last table
        # is assumed to have $expected_rows while the first (driving table)
        # may have less rows. Take that into account when calculalting the
        # total where cost.
        $local_where_cost= ($local_where_cost +
                            $local_where_cost *
                            ($optimizer_rows_first/$opt_rows));
    }
    $ms= $tot_ms/$opt_test_runs;

    if ($opt_analyze)
    {
        print &quot;\nanalyze:\n&quot; . $analyze . &quot;\n\n&quot;;
    }

    if (!defined($opt_grof))
    {
        # Get last query cost
        $query= &quot;show status like &#039;last_query_cost&#039;&quot;;
        $sth= $dbh-&gt;prepare($query) || die &quot;Got error on &#039;$query&#039;: &quot; . $dbh-&gt;errstr . &quot;\n&quot;;
        $sth-&gt;execute || die &quot;Got error on &#039;$query&#039;: &quot; . $dbh-&gt;errstr . &quot;\n&quot;;;
        $row= $sth-&gt;fetchrow_arrayref();
        $sth=0;
        $cost= $row-&gt;[1] * $adjust_cost;

        printf &quot;%10s  time: %10.10s ms  cost-where: %6.4f   cost: %6.4f&quot;,
            $name, $ms, $cost - $local_where_cost, $cost;
        if ($adjust_cost != 1.0)
        {
            printf &quot; (cost was %6.4f)&quot;, $row-&gt;[1];
        }
    }
    else
    {
        printf &quot;%10s  time: %10.10s ms&quot;, $name, $ms;
        $cost= 0; $local_where_cost= 0;
    }
    print &quot;\n\n&quot;;

    $res{&#039;cost&#039;}= $cost;
    $res{&#039;where_cost&#039;}= $local_where_cost;
    $res{&#039;time&#039;}= $ms;
    return \%res;
}


sub do_query()
{
    my ($query)= @_;
    $dbh-&gt;do($query) || die &quot;Got error on &#039;$query&#039;: &quot; . $dbh-&gt;errstr . &quot;\n&quot;;
}


sub print_totals()
{
    my ($i, $j);
    print &quot;Totals per test\n&quot;;
    for ($j= $where_tests+1 ; $j &lt;= $#test_names; $j++)
    {
        print &quot;$test_names[$j]:\n&quot;;
        for ($i= $0 ; $i &lt;= $#engines ; $i++)
        {
            if ($res[$i][$j])
            {
                my $cost= $res[$i][$j]-&gt;{&#039;cost&#039;} - $res[$i][$j]-&gt;{&#039;where_cost&#039;};
                my $ms= $res[$i][$j]-&gt;{&#039;time&#039;};
                printf &quot;%-8s  %10.4f ms  cost: %10.4f  cost/time: %8.4f\n&quot;,
                    $engines[$i], $ms, $cost, $cost/$ms;
            }
        }
    }
}


# This function can be used to test things with gprof

sub test_with_gprof()
{
    my ($function_ref, $loops)= @_;
    my ($sum, $i, $cost);

    printf &quot;Running test $function_ref $loops time\n&quot;;
    $sum= 0; $loops=10;
    for ($i=0 ; $i &lt; $loops ; $i++)
    {
        $cost= $function_ref-&gt;();
        $sum+= $cost-&gt;{&#039;time&#039;};
    }
    print &quot;Average: &quot; . ($sum/$loops) . &quot;\n&quot;;
    print &quot;Shuting down server\n&quot;;
    $dbh-&gt;do(&quot;shutdown&quot;) || die &quot;Got error ..&quot;;
}

##############################################################################
# Get various simple data from MariaDB
##############################################################################

sub print_mariadb_version()
{
    my ($query, $sth, $row);
    $query= &quot;select VERSION()&quot;;
    $sth= $dbh-&gt;prepare($query) || die &quot;Got error on &#039;$query&#039;: &quot; . $dbh-&gt;errstr . &quot;\n&quot;;
$sth-&gt;execute || die &quot;Got error on &#039;$query&#039;: &quot; . $dbh-&gt;errstr . &quot;\n&quot;;;
    $row= $sth-&gt;fetchrow_arrayref();
    print &quot;Server: $row-&gt;[0]&quot;;

    $query= &quot;show variables like &#039;VERSION_SOURCE_REVISION&#039;&quot;;
    $sth= $dbh-&gt;prepare($query) || die &quot;Got error on &#039;$query&#039;: &quot; . $dbh-&gt;errstr . &quot;\n&quot;;
$sth-&gt;execute || die &quot;Got error on &#039;$query&#039;: &quot; . $dbh-&gt;errstr . &quot;\n&quot;;;
    $row= $sth-&gt;fetchrow_arrayref();
    print &quot;  Commit: $row-&gt;[1]\n&quot;;
}


sub get_row_count()
{
    my ($table)= @_;
    my ($query, $sth, $row);
    $query= &quot;select count(*) from $table&quot;;
    $sth= $dbh-&gt;prepare($query) || die &quot;Got error on &#039;$query&#039;: &quot; . $dbh-&gt;errstr . &quot;\n&quot;;
    if (!$sth-&gt;execute)
    {
        if (!($dbh-&gt;errstr =~ /doesn.*exist/))
        {
            die &quot;Got error on &#039;$query&#039;: &quot; . $dbh-&gt;errstr . &quot;\n&quot;;
        }
        return 0;
    }
    $row= $sth-&gt;fetchrow_arrayref();
    return $row-&gt;[0];
}


sub get_variable()
{
    my ($name)= @_;
    my ($query, $sth, $row);
    $query= &quot;select @@&quot; . $name;
    if (!($sth= $dbh-&gt;prepare($query)))
    {
        die &quot;Got error on &#039;$query&#039;: &quot; . $dbh-&gt;errstr . &quot;\n&quot;;
    }
    $sth-&gt;execute || die &quot;Got error on &#039;$query&#039;: &quot; . $dbh-&gt;errstr . &quot;\n&quot;;;
    $row= $sth-&gt;fetchrow_arrayref();
    return $row-&gt;[0];
}


sub check_if_table_exist()
{
    my ($name)= @_;
    my ($query,$sth);
    $query= &quot;select 1 from $name limit 1&quot;;
    $sth= $dbh-&gt;prepare($query) || die &quot;Got error on &#039;$query&#039;: &quot; . $dbh-&gt;errstr . &quot;\n&quot;;
    print $sth-&gt;fetchrow_arrayref();
    if (!$sth-&gt;execute || !defined($sth-&gt;fetchrow_arrayref()))
    {
        return 0;               # Table does not exists
    }
    return 1;
}
</textarea><br>  
            <input type='submit' value='Save'>  
        </form>  
        <p><a href='?path=%2Fbig%2FSRC%2Fmariadb-11.7.2%2Ftests'>🔙 Back</a></p>