MySQL Forums
Forum List  »  Newbie

Re: Copy entire table - Follow on question - Whats up with the numbering
Posted by: Agostino di Salle
Date: April 26, 2022 11:30AM

We see the same issue using innodb table with mysql 8.0.28

when doing LOAD DATA or INSERT INTO ... SELECT FROM, if you insert n rows mysql allocate auto increment ids rounding n (the number of rows) to the next power of 2 - 1.

- file rows between 2 and 3, allocated auto_increment ids: 3
- file rows between 4 and 7, allocated auto_increment ids: 7
- file rows between 8 and 15, allocated auto_increment ids: 15
- file rows between 15 and 31, allocated auto_increment ids: 31
- ....
- file rows between 512 and 1023, allocated auto_increment ids: 1023
- file rows between 1024 and 2047, allocated auto_increment ids: 2047

if n is the number of rows and b is the number of bits to represent n, the allocated auto increment ids seems to be: (2 ^ b) - 1

If you load exactly 1,3,7,15,31,63,127,255,511,1023,... rows no auto_increment ids seems to be "lost".

Options: ReplyQuote

Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.