What better way to retrieve the last id from the table?
I tested these two methods and the orde by took longer, is there any more of these?
Note: this will not 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
None of these alternatives will retrieve the last ID.
Your code is to retrieve the Higher ID. Usually the last and the greatest coincide, but it is not an absolute truth.
To know the last ID automatically entered, the code is this:
SELECT LAST_INSERT_ID;
But keep in mind that only works if you last inserted in the same connection .
Not all flowers: If the last insert added 3 records in the DB, this function returns the first one, not the last one.
As you said it is a separate query, what can help is this query that returns the next automatic ID to be used:
SELECT AUTO_INCREMENT
FROM information_schema.tables
WHERE table_name = 'nome da sua tabela'
AND table_schema = 'nome do seu DB' ;
If you really want to know the last inserted, the only safe feature is to recover the data with LAST_INSERT_ID , or exaggerating, by doing a trigger for this.
For example, look at common situations where the question code will not return the last value entered:
If the last records are deleted, LAST_INSERT_ID will still contain the value entered, but the question code will retrieve the IDs prior to these (which will not match those of the next inserts).
If an insert is made by specifying an ID manually, smaller than the one entered, there is no sure way to know the last one.
Now, you have to think about something important: Depending on the use you are going to make, you do not really need the last inserted, but the existing one. In this case, the question code resolves.
Alternatively, you can use the SELECT AUTOINCREMENT technique above, and subtract 1 to estimate what was the previous one. This also does not guarantee that it really was the last inserted, but is less likely to give side effects.
Another caution to take : It may well happen that some other transaction occurs in the DB, and this value changes before you use it, so anything that runs away from LAST_INSERT_ID can generate race condition . Ideally, do not use the value for anything other than actually referencing the last line entered.
One more alert: (to make life even more complicated) you should only use LAST_INSERT_ID if the last insertion did not give an error, otherwise it may be taking a previous insertion ID. There are people who test if they gave zero to see if they returned an ID or not, but this is not reliable if they do not check if INSERT really succeeded. >
As for performance, the only way to be sure is to benchmark, because it depends on a number of factors.
In MySQL it's pretty easy. I was trying to do the same thing in SQL Server and it's a sacrifice.
In MySQL you just need to use LAST_INSERT_ID () .
Example: SELECT LAST_INSERT_ID() INTO @tabela