By default, my primary keys of InnoDB storage engines are auto increment integers. In effort, to hide the number of rows in the database, application code implements some random generator for the primary key.
This is an example of typical scheme:
CREATE TABLE `MUSIC_LINK` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `project_id` int(11) unsigned DEFAULT NULL, PRIMARY KEY (`id`), ) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=latin1;
I am reading a book and have just found out, that InnoDB uses clustered index sorted by primary key. In essence, this means that the records in database file are indexed and ordered by the primary key value. This is great, as long as the primary key for next record is always bigger than the last record inserted (this happens by default because of auto-increment constraint).
What happens when the primary key is no longer auto-incremented? In order to keep the file sorted by primary key, there must be lots of rewrites every time a primary key smaller than the biggest primary key is inserted.
Am I misunderstanding how clustered indexes work in InnoDB? because this sounds like a giant performance issue.