#!/usr/bin/perl
use DBI;
use Benchmark ':hireswallclock';
use POSIX qw( floor );
use Getopt::Long;

$opt_loop_count=100000;         # number of loops(rows)
$random_loop_count=$opt_loop_count;

$opt_db_host="localhost";
$opt_db_socket="/tmp/mysql.sock";
$opt_db_name="test";
$opt_db_engine="MyISAM";

$opt_rows=50000;
$opt_skip_create='';
$opt_inner_table_size=10;

$opt_mode=$opt_stat="";

GetOptions("inner_table_size=s","db-socket=s","db-engine=s","skip-create","stat");
          
$dbh = DBI->connect("dbi:mysql:$opt_db_name;host=$opt_db_host;mysql_socket=$opt_db_socket",
                    "root","", { PrintError => 0}) or die $DBI::errstr;

$sth = $dbh->prepare("select VERSION()") or die $DBI::errstr;
$version="MySQL ?";
if ($sth->execute && (@row = $sth->fetchrow_array))
{
  $row[0] =~ s/-/ /g;
  $version="MySQL $row[0]";
}
$sth->finish;

print "$version, ENGINE: $opt_db_engine \n";


if ($opt_skip_create eq '')
{

####
#### Generating random keys
####

print "Generating random keys\n";
$random[$random_loop_count]=0;
for ($i=0 ; $i < $random_loop_count ; $i++)
{
  $random[$i]=$i;
}

my $tmpvar=1;
for ($i=0 ; $i < $random_loop_count ; $i++)
{
  $tmpvar^= ((($tmpvar + 63) + $i)*3 % $random_loop_count);
  $swap=$tmpvar % $random_loop_count;
  $tmp=$random[$i]; $random[$i]=$random[$swap]; $random[$swap]=$tmp;
}

sub random {
  my $i = shift;
  $i += $opt_loop_count;
  return $random[$i % $random_loop_count]
           + floor($i/$random_loop_count)*$random_loop_count;
}


####
#### Create needed table
####

print "Creating table bench1\n";
$dbh->do("drop table if exists bench1 ") or die $DBI::errstr;
$dbh->do("drop table if exists bench2 ") or die $DBI::errstr;
$dbh->do("create table bench1 (id int NOT NULL, 
                               id2 int NOT NULL,
                               id3 int NOT NULL,
                               dummy1 char(30),
                               primary key (id,id2),
                               index ix_id3 (id3)) engine=$opt_db_engine") or die $DBI::errstr;


$query="insert into bench1 (id,id2,id3,dummy1) values ";

$total_rows=$opt_loop_count*3;

$opt_loop_count=$opt_loop_count/2;
print "Inserting $opt_loop_count rows in order\n";

$loop_time=new Benchmark;


for ($i=0 ; $i < $opt_loop_count ; $i++)
{
  $sth = $dbh->do($query . "($i,$i,$i,'ABCDEFGHIJ')") or die $DBI::errstr;
}
  
$end_time=new Benchmark;
print "Time for insert_in_order (" . ($opt_loop_count) . "): " .
 timestr(timediff($end_time, $loop_time),"all") . "\n";

}

$dbh->do("drop table if exists bench2 ") or die $DBI::errstr;

$dbh->do("create table bench2 (id int NOT NULL,
                               primary key (id))
                               engine=$opt_db_engine") or die $DBI::errstr;

# Fill join table to have the same id's as 'query'
for ($i=1 ; $i <= $opt_inner_table_size ; $i++)
{
  $dbh->do("insert into bench2 values($i)") or die $DBI::errstr;
}

print "Inner table size: $opt_inner_table_size\n";

$range_loop_count=500;

time_fetch_all_rows(undef,'subselect_exists',
                      "SELECT * FROM bench1 WHERE EXISTS (SELECT * FROM bench2 WHERE bench2.id=bench1.id)",
                      $dbh, $range_loop_count);

$dbh->disconnect();

sub print_stat
{
  $sth=$dbh->prepare("show global status like 'handler%'");
  $sth->execute();
  while ((@arr=$sth->fetchrow_array))
  {
    print join(",",@arr),"\n";
  }
 $sth->finish;
}

sub time_fetch_all_rows
{
  my($test_text,$result_text,$query,$dbh,$test_count)=@_;
  my($i,$loop_time,$end_time,$rows);

  print_stat if ($opt_stat);

  print $test_text . "\n"   if (defined($test_text));
  $rows=0;
  $loop_time=new Benchmark;
  for ($i=1 ; $i <= $test_count ; $i++)
  {
    $sth=$dbh->prepare($query) or die $DBI::errstr;
    $sth->execute() or die $DBI::errstr;
    while ($sth->fetchrow_arrayref)
    {
      $rows++;
    }
    $sth->finish();
  }
  $end_time=new Benchmark;
  print "Time for $result_text ($test_count:$rows): " .
    timestr(timediff($end_time, $loop_time),"all") . "\n\n";

  print_stat if ($opt_stat);
}