Ten MySQL Best Practices
Subject:   Alternative to point number 6
Date:   2005-01-07 00:09:26
From:   Tony Marston
Instead of using an AUTO_INCREMENT column the method I have used for years, and which works on all RDBMS systems, is as follows:

SELECT MAX(id) FROM 'table'

This gives the current highest value. Just add 1 for the next number. Provided that 'id' is an indexed column the query can be satisfied very, very quickly by retrieving the highest value from the index and without performing a full table scan.

A problem I have had with obtaining numbers from a different table is that it is possible to import data into the data table (e.g. from a 'live' to a 'dev' database) which contains id values which are greater than the values on the sequence table, thus causing all future inserts to fail. The above method does not suffer from this problem.

1 to 1 of 1
1 to 1 of 1