I have a table of web pages, with the primary key as hash of the URL, and an auto_increment ID column, which is also a unique key.
What I'm a bit confused by is why successive inserts don't increment the ID field by 1. When I first created the table and did a single insert, the first id was 1. The second insert produced and id of 5 and the third was 8.
I have a trigger on the table which, on insert, computes the hash of the URL of the webpage. Not sure if that's relevant or not.
It's not a problem to have gaps, but I would like to understand why successive inserts don't generate IDs with a step size of 1.
thanks!
Several suggestions of why this may be happening:
See auto_increment_increment. This controls the incrementation each time a new value is requested during INSERT.
Also if you use InnoDB tables in MySQL 5.1, they optimized auto-inc allocation so that it locks the table for a shorter duration. This is good for concurrency, but it can also "lose" auto-inc values if the INSERT of a row conflicts with another constraint such as a secondary UNIQUE column or a foreign key. In those cases, the auto-inc value allocated is not pushed back into the queue, because we assume another concurrent thread may have already allocated the next auto-inc value.
Of course rollbacks also occur, in which case an auto-inc value may be allocated but discarded.