Use the mysql bulk insert statement format if possible.

We found that using the traditional prepare/execute cycle was about 10 times slower to insert 2 million records, than using several bulk sql statements of about 500K.

my @rows = (
    [qw(a b c d)],
    [qw(e f g h)],
    [qw(i j k l)],
    [qw(m n o p)],
    [qw(q r s t)],
    [qw(u v w x)],
    [qw(y z a b)],
    [qw(c d e f)],
    [qw(h i j k)],
    [qw(l m n o)],
  );
  
# -------------------------------------
# slower
# -------------------------------------
my $sth = $db->prepare("INSERT INTO mytable VALUES(?,?,?,?)")
  or die "prepare failed: " . $db->errstr;
 
for my $row ( @rows ) {
 $sth->execute(@$row);
}

# -------------------------------------
# faster
# -------------------------------------
my $sql = "INSERT INTO mytable VALUES ";
for my $row ( @rows ) {
 $sql .= "('" . join("','",@$row) . '),";
}
substr($sql,-1)='';

$db->do( $sql );
[download]

And if using a bulk insert isn't possible for some reason, first drop the indices on the table(s) involved, insert your data, then rebuild the indices. Unless the tables already contain billions of rows, rebuilding the indices is faster than modifying them millions of times.

Abigail