MySQL Forums
Forum List  »  Optimizer & Parser

Re: Insert multiple values within a transaction with LAST_INSERT_ID
Posted by: Matthias Bendewald
Date: April 18, 2011 04:46AM

Hello Rick,
thank you for your answer.
Quote

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;
Exactly what happens here!
Quote

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.
Yes, i suspected an fsync related issue. Battery Backed Write Cache might be an option in the future, the actual server does not provide that.
The action takes place in one commit already.

Quote

SHOW VARIABLES LIKE 'innodb%';
...
'innodb_buffer_pool_size', '635437056'
...
'innodb_flush_log_at_trx_commit', '1'
...
Quote

SHOW VARIABLES LIKE '%sync%';
'innodb_sync_spin_loops', '20'
'sync_binlog', '0'
'sync_frm', 'ON'

Table1:
Quote

CREATE TABLE `bauteil` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`lieferung` int(11) unsigned NOT NULL,
`lagerort` int(11) NOT NULL,
`status` int(11) NOT NULL,
`produktion` int(11) DEFAULT NULL,
`kommentar` varchar(10000) DEFAULT NULL,
`seriennr_hersteller` varchar(60) DEFAULT NULL,
`st_ok` tinyint(1) NOT NULL DEFAULT '0',
`st_mod` tinyint(1) NOT NULL DEFAULT '0',
`st_damage` tinyint(1) NOT NULL DEFAULT '0',
`st_calibrated` tinyint(1) NOT NULL DEFAULT '0',
`st_burnin` tinyint(1) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`,`status`) USING BTREE,
KEY `FK_bauteil_2` (`lagerort`),
KEY `FK_bauteil_3` (`status`),
KEY `FK_bauteil_1` (`lieferung`) USING BTREE,
KEY `FK_bauteil_produktion` (`produktion`),
CONSTRAINT `FK_bauteil_2` FOREIGN KEY (`lagerort`) REFERENCES `lagerorte` (`id`),
CONSTRAINT `FK_bauteil_3` FOREIGN KEY (`status`) REFERENCES `bauteil_status` (`id`),
CONSTRAINT `FK_bauteil_lieferung` FOREIGN KEY (`lieferung`) REFERENCES `lieferung` (`id`),
CONSTRAINT `FK_bauteil_produktion` FOREIGN KEY (`produktion`) REFERENCES `produktion` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=65142 DEFAULT CHARSET=utf8 ROW_FORMAT=FIXED

Table 2:
Quote

CREATE TABLE `bauteil_produkt` (
`id` int(11) NOT NULL,
`produkt` int(11) NOT NULL,
`seriennr` int(11) NOT NULL,
`start_time` datetime NOT NULL,
`ersteller` int(11) NOT NULL,
`outdated` tinyint(2) NOT NULL DEFAULT '0',
PRIMARY KEY (`produkt`,`seriennr`,`outdated`) USING BTREE,
UNIQUE KEY `FK_bauteil_produkt_bt` (`id`,`outdated`) USING BTREE,
KEY `FK_bauteil_produkt_ersteller` (`ersteller`),
CONSTRAINT `FK_bauteil_produkt_bt` FOREIGN KEY (`id`) REFERENCES `bauteil` (`id`),
CONSTRAINT `FK_bauteil_produkt_ersteller` FOREIGN KEY (`ersteller`) REFERENCES `personen` (`id`),
CONSTRAINT `FK_bauteil_produkt_produkt` FOREIGN KEY (`produkt`) REFERENCES `produkt` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=FIXED

Quote

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.
Yeah, that's what i already thought of, but i have no idea how to get the correct key (id) for the batched insert in table2 since MySQL will autoincrement it in table1.

BTW are there other "boxes" available than "quote" in this forum? I guessed source, but guessed wrong...

Best wishes, thank you so far!

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Insert multiple values within a transaction with LAST_INSERT_ID
2182
April 18, 2011 04:46AM


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.