Mit DELETE FROM, Tabellen sind leer

Benchmark: timing 50000000 iterations of AutoIncr, Redesign...
  AutoIncr:  0 wallclock secs ( 1.58 usr +  0.00 sys =  1.58 CPU) @ 31685678.07/s (n=50000000)
  Redesign:  0 wallclock secs ( 0.12 usr + -0.01 sys =  0.11 CPU) @ 454545454.55/s (n=50000000)
            (warning: too few iterations for a reliable count)
                Rate AutoIncr Redesign
AutoIncr  31685678/s       --     -93%
Redesign 454545455/s    1335%       --

Ohne DELETE FROM
Benchmark: timing 50000000 iterations of AutoIncr, Redesign...
  AutoIncr:  3 wallclock secs ( 2.53 usr +  0.00 sys =  2.53 CPU) @ 19747235.39/s (n=50000000)
  Redesign:  0 wallclock secs ( 0.06 usr +  0.00 sys =  0.06 CPU) @ 806451612.90/s (n=50000000)
            (warning: too few iterations for a reliable count)
                Rate AutoIncr Redesign
AutoIncr  19747235/s       --     -98%
Redesign 806451613/s    3984%       --




Wiederholung:
Benchmark: timing 50000000 iterations of AutoIncr, Redesign...
  AutoIncr:  1 wallclock secs ( 1.02 usr +  0.00 sys =  1.02 CPU) @ 49261083.74/s (n=50000000)
  Redesign:  1 wallclock secs ( 1.00 usr +  0.00 sys =  1.00 CPU) @ 50000000.00/s (n=50000000)
               Rate AutoIncr Redesign
AutoIncr 49261084/s       --      -1%
Redesign 50000000/s       1%       --

#/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