other dbs seem to support a table we don't

We can remove the requirement that AUTO_INCREMENT generate an index, it is not required.

On Thu, 03 Feb 2011 03:23:25 -0000, Brian Aker <email address hidden> wrote:
> We can remove the requirement that AUTO_INCREMENT generate an index, it
> is not required.

This would cause problems for InnoDB as the way it gets the max auto inc
number on startup is to do a reverse index scan reading the last record.

--
Stewart Smith

The following should be added to docs:

InnoDB (and HailDB) don't store a current auto_increment value. In fact, the lower levels know absolutely nothing about auto_increment. The auto_increment feature is entirely implemented in the interface to Drizzle from the lower level storage. On opening a table, the index on the auto_increment column is found and then InnoDB/HailDB reads the last record in that index and sets the value of the auto_increment field in that row to the last used auto_increment value.

To support auto_increment on non-index fields, we would either have to maintain a current auto_increment number in another (system) table - having to update it in a sep txn for each auto_increment operation and generating lots of unneeded undo log OR on opening the table do a full table scan to find the current auto_increment value.