MYSQL - What is faster? Insert into with auto increment or update + select?

Question

I have simple question.

What is faster for performance?

A) Blank "insert into" with auto-increment to INNODB. (only 1 column in table, id(int)) and return that last id via PHP function mysql_insert_id();

 $db=mysql_query("insert into `system_id` () VALUES()",$GLOBALS["dbspojenie"]);               
 $id = mysql_insert_id();
 return $id;

B) UPDATE id and after, select that id from same row? (MYISAM)

  $db=mysql_query("UPDATE `system` SET `id`=`id`+1",$GLOBALS["dbspojenie"]);
  $db=mysql_query("select `id` from `system`",$GLOBALS["dbspojenie"]);
  while($zaznam=mysql_fetch_array($db)): 
  $id=$zaznam["id"];
  endwhile;  
  return $id;

I have used INNODB in A) because of row locking.

It is just question, thank you for answering. :)

B is unsafe in a multi-user scenario, and the second query has a horrendous overhead in the number of returned rows and the loop anyway.... do you really believe that it could possibly be faster when you have 100 results returned, 1000 results returned, 1000000 results returned?.... at the very least, select max(`id`) as 'id' from system`` would only return a single result, so you'd eliminate the loop, but it's still unsafe — Mark Baker
– Mark Baker, Commented Apr 30, 2015 at 8:28
And why are you using MySQL rather than MySQLi or PDO with prepared statements/bind variables anyway.... it's 2015 now, not 2005 — Mark Baker
– Mark Baker, Commented Apr 30, 2015 at 8:31
thank you for replies, it is old machine with older mysql and php... i will go for mysqli or PDO for sure after some time, but now i need to solve this... — Roman Holic
– Roman Holic, Commented Apr 30, 2015 at 8:33
If you've written the code, why now throw it in a million-count loop and look at the results yourself? — moopet
– moopet, Commented Apr 30, 2015 at 8:59

Izzy · Accepted Answer · 2015-04-30 09:07:08Z

1

According to your code, I presume the first INNODB example will be faster. In second example you have UPDATE and SELECT vs one INSERT in first example.

And don't forget that MYISAM uses table level locking vs INNODB's row level lock. If you have way lot of reads from the table I would use MYISAM, but in other circumstances (like this one) I would prefer INNODB.

And as Mark stated in comment above, SELECTing table without LIMIT would select complete table. Now think about 1 billion rows table. What is faster: selection of 1 billion rows or 1 insert?

edited Apr 30, 2015 at 9:07

answered Apr 30, 2015 at 8:57

Izzy

4127 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Roman Holic Over a year ago

izzy thank you for your answer... this table is made only for adding new ids and geting it back, so that why i use innodb, no reading, only returning that last id with php function mysql_insert_id()

Collectives™ on Stack Overflow

MYSQL - What is faster? Insert into with auto increment or update + select?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related