MySQL Forums
Forum List  »  Optimizer & Parser

Re: Insert multiple values within a transaction with LAST_INSERT_ID
Posted by: Rick James
Date: April 16, 2011 08:12AM

Is this a more complete example:
BEGIN;
INSERT INTO table1 (...) VALUES (...);
INSERT INTO table2 (table1_id, ...) VALUES (LAST_INSERT_ID(), ...);
INSERT INTO table1 (...) VALUES (...);
INSERT INTO table2 (table1_id, ...) VALUES (LAST_INSERT_ID(), ...);
INSERT INTO table1 (...) VALUES (...);
INSERT INTO table2 (table1_id, ...) VALUES (LAST_INSERT_ID(), ...);
...
COMMIT;
80/sec sounds like an fsync to a SATA disk without a write cache. I would expect something like 80 for AUTOCOMMIT=1 and no BEGIN. RAID with Battery Backed Write Cache can increase that significantly. And so will (should) the BEGIN.

Please provide
SHOW VARIABLES LIKE 'innodb%';
SHOW VARIABLES LIKE '%sync%';
I'll be looking for
innodb_flush_log_at_trx_commit
innodb_buffer_pool_size
sync_binlog

Another possible reason is secondary indexes. If you have big tables, and 'random' indexes (such as md5, guid, uuid, etc), updating an index requires read-modify-write for each such index; this could be a disk hit (ordinary disk run about 80/sec). Please provide
SHOW CREATE TABLE table1
SHOW CREATE TABLE table2

Yet another thought:
INSERT INTO table1 VALUES (key1, val1), (key2, val2), ... -- all of these at once
SELECT key, val FROM table1 WHERE key IN (key1, key2, ...)
INSERT INTO table2 VALUES (...,key1,...), (...,key1,...), ... -- all the rows.
That's a total of 3 statements, not 2N. Batched INSERTs run faster. I recommend 100 at a time, which may give 10x speedup.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Insert multiple values within a transaction with LAST_INSERT_ID
3083
April 16, 2011 08:12AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.