Bug #2806 Different time on LAST_INSERT_ID and mysql_insert_id

• Submitted: 15 Feb 2004 9:10
• Modified: 16 Feb 2004 1:28
• Reporter: Marcin Giedz
• Status: Not a Bug
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 4.0.18
• OS: Linux (Linux 2.4.24 Debian)
• CPU Architecture: Any

[15 Feb 2004 9:10] Marcin Giedz

Description:
I use this simply code to determine last id given by insert data: 
	int insert_id; 
	char *query, *encdata; 
	int datasize; 
	MYSQL_RES *res; 
	MYSQL_ROW row; 
	 
	static MYSQL mydb; 
	if (!mysql_real_connect(&mydb, "10.10.2.1", "root", "", "test", 3306, 
"", 0)) { 
		printf(mysql_error(&mydb)); 
		exit(1); 
	} 
	else { 
		printf("Connected to mysql.\n"); 
	} 
	query=malloc(255); 
	for (a = 1; a < 100000; a++) { 
		sprintf(query,"INSERT INTO test.tfn3 VALUES 
(0,-1,'xxx',now())"); 
		if(mysql_real_query(&mydb, query, strlen(query)+255)) { 
			printf(mysql_error(&mydb)); 
			exit(1); 
		} 
		insert_id=mysql_insert_id(&mydb); 
		printf("%d,",insert_id); 
	} 
	free(query); 
	mysql_close(&mydb); 
	return 0; 
 
As you can see I insert 100k record into my table tfn3 -  
CREATE TABLE `tfn3` ( 
  `uid` int(11) NOT NULL auto_increment, 
  `iduzytkownik` int(11) NOT NULL default '0', 
  `uzytkownik` varchar(50) NOT NULL default '-', 
  `dataedycji` datetime NOT NULL default '0000-00-00 00:00:00', 
  PRIMARY KEY  (`uid`) 
) TYPE=MyISAM;  
 
It lasts about 1 minut on my Linux Box. But when I try LAST_INSERT_ID 
function: 
		sprintf(query,"SELECT LAST_INSERT_ID() FROM test.tfn3"); 
		if(mysql_real_query(&mydb, query, 255)) { 
			printf(mysql_error(&mydb)); 
			exit(1); 
		} 
		res=mysql_store_result(&mydb); 
		row=mysql_fetch_row(res); 
		printf("%s,",row[0]); 
		mysql_free_result(res); 
		//insert_id=mysql_insert_id(&mydb); 
		//printf("%d,",insert_id); 
	} 
	free(query); 
	mysql_close(&mydb); 
	return 0; 
 
it lasts about 5 minut to insert 15k record.... and about 4 hours to put all 
100k!!!!! 
In my work I use Delphi and ZEOS controls for connection to MYSQL. In the 
beginning I thought it's zeos fault that makes so big delay. But tooday I 
checked and my result are above. Please If you can find why LAST_INSERT_ID 
take so long time. Thanks,Marcin Giedz 

How to repeat:
Use code I put into Description!!!

[16 Feb 2004 1:23] Alexander Keremidarski

Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Additional info:

This is expected because of a way you are using LAST_INSERT_ID();

SELECT LAST_INSERT_ID() FROM table; 
You select as much rows as there are in table at the moment. I.e. at each iteration you select "a" rows

for (a = 1; a < 100000; a++)

INSERT .. 1 row .. 
SELECT LAST_INSERT_ID() FROM table; -> rows == a

[16 Feb 2004 1:28] Alexander Keremidarski

Forgot to mention that with appropriate query:

    sprintf(query,"SELECT LAST_INSERT_ID()");

I got 100k rows inserted in 25 sec compared to 15 sec without LAST_INSERT_ID part.  I hope you will agree this is expected difference.