#/usr/bin/perl

use strict;
use warnings;
use Benchmark qw(cmpthese timethese);
use DBI;

my $ins = 'abcde';



my $DBH = dbh();
my $STH = {
	INS_AUTO => $DBH->prepare("INSERT INTO test(text)VALUES(?)"),
	IS_DRIN  => $DBH->prepare("SELECT id  FROM test WHERE text=?"),
	LAST_ID  => $DBH->prepare("SELECT LAST_INSERT_ID()"),
	REDESIGN => $DBH->prepare(q(
		INSERT INTO redesign(text)VALUES(?)
		ON Duplicate Key UPDATE text=?
	)),

};

# bisherige Lösung, vorh. Abfrage | Last_Insert_ID
sub autoincr{
	$DBH->do("DELETE FROM test");
	$STH->{IS_DRIN}->execute($ins);
	my $id = 0;
	if($id = $STH->{IS_DRIN}->fetchrow_array){
		return $id;
	}
	else{
		$STH->{INS_AUTO}->execute($ins);
		$id = $STH->{LAST_ID}->execute;
		$id = $STH->{LAST_ID}->fetchrow_array;
		return $id;
	}
}

# Lösung nach redesign
sub redesign{
	$DBH->do("DELETE FROM redesign");
	$STH->{REDESIGN}->execute($ins, $ins);
	return $ins;
}

cmpthese(50_000_000, {
	'Redesign' => &redesign,
	'AutoIncr' => &autoincr,
});



sub dbh{
	my %cfg = (
		base => 'myweb',
		host => 'localhost',
		port => 3306,
		user => '',
		pass => '',

	@_);
	my $dbh = undef;
	eval{
		$dbh = DBI->connect("DBI:mysql:$cfg{base}:$cfg{host}:$cfg{port}", $cfg{user}, $cfg{pass},
			{RaiseError => 1, PrintError => 0}
		);
	};
	return $@ ? undef : $dbh;
}

__END__
mysql> show create table test;

CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `text` varchar(20) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=99 DEFAULT CHARSET=utf8 


mysql> show create table redesign;

redesign | CREATE TABLE `redesign` (
  `text` varchar(29) NOT NULL DEFAULT '',
  PRIMARY KEY (`text`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8