optimized way to recover last mysql ID

Question

Which is the most optimized way to recover the last table id?

I tested these two methods and the orde by took a long time. There is some beyond these?

Note: nãp will be a query after Insert. It will be a separate query.

select max(campo_id) from tabela

select campo_id FROM tabela ORDER BY campo_id DESC LIMIT 1

Answer 1

None of these alternatives serves to recover the last ID.

Your code is to recover the Highest ID. Usually the last and the largest coincide, but it is not an absolute truth.

To know the last ID inserted automatically, the code is this:

SELECT LAST_INSERT_ID;

Still remembering that you don’t even need the SELECT in most situations, if you are using a Mysql library in another language, or even the connector in C, it is common to have a specific function that already returns the value right after the INSERT.

But keep in mind that this only works if you made the last insertion on the same connection.

It’s not all flowers: if the last insert added 3 records in DB, this function returns the first of them, not the last.

As you said it is a separate query, what can help is this query that returns the next Automatic ID that will be used:

SELECT AUTO_INCREMENT
FROM   information_schema.tables
WHERE  table_name = 'nome da sua tabela'
AND    table_schema = 'nome do seu DB' ;

But then, what’s the problem with the question code?

If you really want to know the last entered, the only safe feature is to recover the data with LAST_INSERT_ID, or exaggerating, making a Trigger for this.

For example, see common situations where the question code will not return the last entered value:

• If the last records are deleted, the LAST_INSERT_ID will still contain the entered value, but the question code will retrieve the ID previous to these (which will not match the next insertions).

• If an insert is made by specifying a manually smaller ID than the entered one, there is no safe way to know which was the last one.

Now, you have to think of something important: Depending on the use you are going to make, you don’t really need the last inserted, but the largest existing. In this case, the question code solves.

Alternatively, you can use the technique of SELECT AUTOINCREMENT above, and subtract 1 to estimate what the previous one was. This also does not guarantee which actually was the last inserted but is less likely to give side effects.

Another care to take: may well happen to occur some other transaction in DB, and this value change before you use, so anything that escapes from the LAST_INSERT_ID can generate a race condition. The ideal is not to use the value for anything other than actually referencing the last line inserted.

Another warning: (to further complicate life) you should only use the LAST_INSERT_ID if the last insertion was not an error, otherwise you may be picking up a previous insertion ID. There are people who forehead turned to zero to know if you returned an ID or not, but this unreliable if it does not check whether the INSERT really succeeded.

As for the performance, the only way to be sure is by doing benchmarks, because it depends on a number of factors.

Answer 2

Mysql is pretty easy. I was trying to do the same thing on SQL Server and it’s a sacrifice.

In Mysql you only need to use LAST_INSERT_ID().

Example: SELECT LAST_INSERT_ID() INTO @tabela