Bug #56175 SELECT last_insert_id() slows down application
Submitted: 22 Aug 2010 23:48 Modified: 23 Sep 2010 17:31
Reporter: Louis Breda van Email Updates:
Status: No Feedback Impact on me:
Category:MySQL Server: InnoDB storage engine Severity:S3 (Non-critical)
Version:5.5.5 OS:Any
Assigned to: CPU Architecture:Any
Tags: last_insert_id(), performance

[22 Aug 2010 23:48] Louis Breda van

Perhaps not exactly a bug, but never the less something which needs attention.

At this moment I am looking into the performance of a DB, trying to discover why it is so relatively slow.

One of the things which turns out to slow down the DB very significant is the fact that I use a last_insert_id() query to fetch the PK from the first inserted record in order to store that value with a second insert.

Analysing the performance it turned out that the
"SELECT last_insert_id()" did cost about as much time if not more than the two insert statements together !!

So, if I could leave out the "SELECT last_insert_id()" (which I can't) that would speed up the DB a factor two !!


How to repeat:
Using Visualbasic.net 2010 and ODBC

rs1 has an autoincrement PK

rs1("fields a,b,c") = a,b,c 

PK_RS1 = cnObjDate.Execute("SELECT last_insert_id()").Fields(0).Value()

rs2("fields x,y,z") = x,y,z 
rs2("RefKey") = PK_RS1

Suggested fix:
It ^feels^ like the database is performing a sort of Select Max(PK) form RS1 where connection. 

Where it could probably have that autoincrement value stored somewhere with the connection
[23 Aug 2010 17:07] Sveta Smirnova
Thank you for the report.

This can depend from table definition as well. Please send us output of SHOW CREATE TABLE table_you_just_inserted_a_row
[23 Aug 2010 17:19] Louis Breda van

I do not think it is related to the table def, which is not at all exotic at all. But as requested you can find the table def below.


DROP TABLE IF EXISTS `myschema`.`fileobjdata`;
CREATE TABLE  `myschema`.`fileobjdata` (
  `RawObjId` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `FileId` char(32) NOT NULL,
  `AbsDayFile` int(10) unsigned DEFAULT NULL,
  `FileObjNo` int(10) unsigned NOT NULL,
  `ObjData` varchar(3000) DEFAULT NULL,
  `LenData` int(10) unsigned DEFAULT NULL,
  KEY `IdxFileID` (`FileId`),
  KEY `IdxAbsDayFile` (`AbsDayFile`)
[23 Aug 2010 17:31] Sveta Smirnova
Thank you for the feedback.

I can not repeat described behavior and LAST_INSERT_ID should not run any MAX(id) queries or like, but rather use value inserted during session. So this looks like application/NET/ODBC issue for me. Could you please turn general log to on and send us statements which your application sends to the server?
[23 Sep 2010 23:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".