#!/usr/bin/perl
#
# Test of selecting on keys that consist of many parts
#
$opt_loop_count=10000;
$opt_regions=6;
$opt_grps=100;
##################### Standard benchmark inits ##############################

use Mysql;
use Getopt::Long;
use Benchmark;

package main;

$opt_skip_create=$opt_skip_in=$opt_skip_delete=$opt_verbose=$opt_fast_insert=
  $opt_lock_tables=$opt_debug=$opt_skip_delete=$opt_fast=$opt_skip_drop=$opt_force=0;
$opt_host=""; $opt_db="test";

GetOptions("host=s","db=s","skip-create","skip-in","skip-delete","skip-drop","verbose","fast-insert","lock-tables","debug","fast","force") || die "Aborted";
$opt_verbose=$opt_fast=$opt_force=$opt_fast_insert=undef; # Ignore warnings from these

print "Testing the speed of selecting on keys that consist of many parts\n";
print "The tests are done with a table that has $opt_loop_count rows.\n\n";

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

$start_time=new Benchmark;
$dbh = Mysql->Connect($opt_host, $opt_db) || die $Mysql::db_errstr;

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

goto select_test if ($opt_skip_create);

print "Creating table\n";
$Mysql::QUIET = 1;
$dbh->Query("drop table bench");
$Mysql::QUIET = 0;

do_query("create table bench (region char(1),id int(5),rev_id int(5), grp int(5), primary key (region,id), unique(region,rev_id), unique (region,grp,id))",$dbh);

if ($opt_lock_tables)
{
  do_query("LOCK TABLES bench WRITE",$dbh);
}

####
#### Insert $opt_loop_count records with
#### region:	"A" -> "E"
#### id: 	0 -> count
#### rev_id:	count -> 0,
#### grp:	distributed values 0 - > count/100
####

print "Inserting $opt_loop_count rows\n";

$loop_time=new Benchmark;
$query="insert into bench 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_grps;
  $region=chr(65+$id%$opt_regions);
  do_query("$query'$region',$id,$rev_id,$grp)",$dbh);
  if ($id == $half_done)
  {				# Test with different insert
    $query="insert into bench (region,id,rev_id,grp) values (";
  }
}

$end_time=new Benchmark;
print "Time to insert $opt_loop_count rows: " .
    timestr(timediff($end_time, $loop_time),"noc") . "\n";

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

select_test:

print "Testing big selects on the table\n";
$loop_time=new Benchmark;

for ($i=0 ; $i < $opt_loop_count/100 ; $i++)
{
  $grp=$i*11 % $opt_grps;
  $region=chr(65+$i%($opt_regions+1));	# One larger to test misses
  do_query("select id from bench where region='$region'",$dbh);
  do_query("select id from bench where region='$region' and id=$i",$dbh);
  do_query("select id from bench where region='$region' and rev_id=$i",$dbh);
  do_query("select id from bench where region='$region' and grp=$grp",$dbh);
  do_query("select id from bench where region>='B' and region<='C' and grp=$grp",$dbh);
  do_query("select id from bench where region>='B' and region<='E' and grp=$grp",$dbh);
  do_query("select id from bench where grp=$grp",$dbh); # This is hard
}

$end_time=new Benchmark;
print "Time for select_big: " .
    timestr(timediff($end_time, $loop_time),"noc") . "\n";

# Test select with many OR's

$loop_time=new Benchmark;
$tmpvar=0;
for ($i=0 ; $i <= $opt_loop_count/1000 ; $i++)
{
  $region=chr(65+$i%($opt_regions+1));	# One larger to test out-of-regions
  $query="select * from bench where ";
  $or_part="grp = 1";
  $or_part2="region='A' and grp=1";

  for ($j=1 ; $j < 50 ; $j++)
  {
    $tmpvar^= ((($tmpvar + 63) + $j)*3 % 100000);
    $tmp=$tmpvar % $opt_grps;
    $tmp_region=chr(65+$tmpvar%$opt_regions);
    $or_part.=" or grp=$tmp";
    $or_part2.=" or region='$tmp_region' and grp=$tmp";
  }
  $or_part="region='$region' and ($or_part)";
  if (!$opt_skip_in)
  {
# Same query, but use 'IN' instead.
    $in_part=$or_part;
    $in_part=~ s/ = / IN \(/;
    $in_part=~ s/ or grp=/,/g;
    $in_part.= ")";
    do_query($query . $in_part,$dbh);
  }
  for ($j=0; $j < 10 ; $j++)
  {
    do_query($query . $or_part,$dbh);
    do_query($query . $or_part2,$dbh);
# Do it a little harder by setting a extra range
    do_query("$query ($or_part) and id < 50",$dbh);
    do_query("$query (($or_part) or (region='A' and grp < 10)) and region <='B'",$dbh);
  }
}

$end_time=new Benchmark;
print "Time for select_range: " .
    timestr(timediff($end_time, $loop_time),"noc") . "\n";

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

if ($opt_lock_tables)
{
  do_query("UNLOCK TABLES",$dbh);
}
if (!$opt_skip_drop)
{
  do_query("drop table bench",$dbh);
}

$dbh=0;				# close connection
$end_time=new Benchmark;

print "Total time: " .
  timestr(timediff($end_time, $start_time),"noc") . "\n";

exit 0;

sub do_query
{
  my($query,$dbh)=@_;
  my($sth);
  print $query if ($opt_debug);
  $Mysql::QUIET = 1;
  $sth=$dbh->Query($query) or
    die "\nError executing '$query':\n$Mysql::db_errstr\n"; 
  if ($opt_debug)
  {
    if (defined($sth))
    {
      print ": " . $sth->numrows . "\n";
    }
    else
    {
      print ": empty set\n";
    }
  }
  $Mysql::QUIET = 0;
}
