Archive for the ‘insert’ Tag

Perl DBI Last Insert ID

Recently I was re-searching for an Perl DBI corenspondent for PHP’s last_insert_id(), so here’s what I’ve found:

$dbh->do('INSERT INTO some_table ...');
my $id = $dbh->last_insert_id(undef, undef, qw(some_table some_table_id)) or die "can not get it?";

Another way:

my $id = $dbh->{ q{mysql_insertid}};

The two undefs are for catalog and schema which don’t appear to be necessary. The DBI docs say they can be ignored for MySQL so you might not even need the table and field parameters.

How to speed up execution of mysql INSERT statements

A very good approach to speed up the execution of an INSERT statement
is to temporarly disable the keys (when executing your query), this
will help MySQL to concetrate on the INSERT and not building indexes at the same
time. Also you should LOCK your tables.

The basic syntax of such a simple operation is listed below:

ALTER TABLE table_name DISABLE KEYS;

LOCK TABLES table_name WRITE;

LOAD DATA INFILE file_name INTO TABLE table_name

UNLOCK TABLES;

ALTER TABLE table_name ENABLE KEYS;