MySQL Bugs: #56175: SELECT last_insert_id() slows down application

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:	None
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

Description:
Hello,

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 !!

Louis

How to repeat:
Using Visualbasic.net 2010 and ODBC

rs1 has an autoincrement PK

rs1.addnew
rs1("fields a,b,c") = a,b,c 
rs1.update

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

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

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

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

Sveta,

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.

Louis

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,
  PRIMARY KEY (`RawObjId`) USING BTREE,
  KEY `IdxFileID` (`FileId`),
  KEY `IdxAbsDayFile` (`AbsDayFile`)
) ENGINE=InnoDB AUTO_INCREMENT=7576 DEFAULT CHARSET=latin1;

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?

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".