Re: InnoDB and indexes

[Jan Lindström]

Yes InnoDB does auto indexing. Every InnoDB table has a special index called the clustered index where the data for the rows is stored. If you define a PRIMARY KEY on your table, the index of the primary key is the clustered index.

If you do not define a PRIMARY KEY for your table, MySQL picks the first UNIQUE index that has only NOT NULL columns as the primary key and InnoDB uses it as the clustered index. If there is no such index in the table, InnoDB internally generates a clustered index where the rows are ordered by the row ID that InnoDB assigns to the rows in such a table. The row ID is a 6-byte field that increases monotonically as new rows are inserted. Thus the rows ordered by the row ID are physically in the insertion order.

Jan Lindström
Oracle Corp./Innobase Oy
InnoDB - transactions, row level locking, and foreign keys for MySQL