#/usr/bin/perl use strict; use warnings; use Benchmark qw(cmpthese timethese); use DBI; my $ins = 'abcde'; my $loop_auto = 0; my $loop_rosti = 0; 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=? )), }; cmpthese(250000, { 'Redesign' => sub { $loop_rosti++; my $test = $ins . ($loop_rosti % 2 ? $loop_rosti : $loop_rosti - 1); $STH->{REDESIGN}->execute($test, $test); }, 'AutoIncr' => sub { my $test = $ins . ($loop_auto % 2 ? $loop_auto : $loop_auto - 1);; $STH->{IS_DRIN}->execute($test); my $id = 0; if($id = $STH->{IS_DRIN}->fetchrow_array){ } else{ $STH->{INS_AUTO}->execute($test); $id = $STH->{'mysql_insertid'}; } }, } ); sub dbh{ my %cfg = ( base => 'testincr', 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__ CREATE TABLE `test` ( `id` int(11) NOT NULL AUTO_INCREMENT, `text` varchar(20) NOT NULL DEFAULT '', PRIMARY KEY (`id`), key(`text`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; CREATE TABLE `redesign` ( `text` varchar(29) NOT NULL DEFAULT '', PRIMARY KEY (`text`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8;