#!/usr/bin/perl use DBI; use Benchmark ":hireswallclock"; use Getopt::Long; $opt_loop_count=100000; # number of loops(rows) $opt_db_host="localhost"; $opt_db_socket="/tmp/mysql.sock"; $opt_db_name="test"; $opt_db_engine="Maria"; $opt_skip_insert=""; GetOptions("skip-insert","db-socket=s","db-engine=s"); $dbh = DBI->connect("dbi:mysql:$opt_db_name;host=$opt_db_host;mysql_socket=$opt_db_socket","root","") 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; # To get better tables with long names $version="MySQL $row[0]"; } $sth->finish; print "$version, ENGINE: $opt_db_engine \n"; goto select_range if ($opt_skip_insert); $dbh->do("drop table if exists bench1"); $dbh->do("create table bench1 (id int NOT NULL, dummy1 int not null, primary key (id) ) engine=$opt_db_engine") or die "Error: $DBI::errstr"; $query="insert into bench1 (id,dummy1) values "; print "Inserting $opt_loop_count rows in order\n"; for ($i=0 ; $i < $opt_loop_count ; $i++) { $sth = $dbh->do($query . "($i,1)") or die $DBI::errstr; } select_range: $range_loop_count=500; for ($i=1 ; $i < $range_loop_count ; $i++) { $start=$opt_loop_count/$range_loop_count*$i; $end=$start+$i; $query="select dummy1 from bench1 where id>=$start and id <= $end"; $sth= $dbh->prepare($query) or die "Error: $DBI::errstr"; $n=$sth->execute or die "Error: $DBI::errstr"; print "select dummy1 from bench1 where id>=$start and id <= $end count:$n\n"; if ($n==0) { print "ERROR: Query $query should return rows $i, $start, $end\n"; exit; } while ($sth->fetchrow_arrayref) { $count++; } $sth->finish; } $dbh->disconnect();