MySQL auto_increment vs max

Question

Alright, which is faster and better to use?

Auto_increment

or

Call an sql which which does a max and then add one

So ultimately the question is should you use mysql to do it or do it yourself?

Answer 1

Definitely auto-increment. The SQL you would write is not thread-safe, and would only apply to the data that enters through your specific stored procedure/sql.

Answer 2

auto_increment will be faster than a max aggregate which will have to do an index look up at the least or a full table scan at worst. Additionally, max() may not be thread safe unless you are very very careful.

Answer 3

The problem with using MAX is the isolation level - at best, there's a chance of the value getting over-inflated due to reading records that may be incorrect. At worst, the query to fetch the current max value is reading the table before a previous insert occurs - causing a collision.

Another consideration is having to roll your own id generator and stuff to retrieve the id because you can't use LAST_INSERT_ID().