CODEWITHSUNDEEP
mysql
Kache
Kache

Reputation: 16677

Why mysql auto_increment skips ids when I `INSERT INTO table () SELECT NULL FROM table`?

I'd like to understand a side-effect of something I was working on.

I wanted to create a large (2+ million) test table of random integers, so I ran the following:

CREATE TABLE `block_tests` (`id` int(11) DEFAULT NULL auto_increment PRIMARY KEY, `num` int(11)) ENGINE=InnoDB;

INSERT INTO `block_tests` (`num`) VALUES(ROUND(RAND() * 1E6));

-- every repeat of this line doubles number of rows;
INSERT INTO block_tests (num) SELECT ROUND(RAND() * 1E6) FROM block_tests;

INSERT INTO block_tests (num) SELECT ROUND(RAND() * 1E6) FROM block_tests;
INSERT INTO block_tests (num) SELECT ROUND(RAND() * 1E6) FROM block_tests;
-- etc

The table size correctly doubles every iteration. What's strange are the ids of the rows that have been added:

mysql> select * from block_tests limit 17;
+----+--------+
| id | num    |
+----+--------+
|  1 | 814789 |
|  2 |  84489 |
|  3 | 978078 |
|  4 | 636924 |
|  6 | 250384 |
|  7 | 341151 |
|  8 | 954604 |
|  9 | 749565 |
| 13 | 884014 |
| 14 | 171375 |
| 15 | 204833 |
| 16 | 510040 |
| 17 | 935701 |
| 18 | 148383 |
| 19 | 934814 |
| 20 | 228923 |
| 28 | 340170 |
+----+--------+
17 rows in set (0.00 sec)

For some reason, there are skips in the ids. There's a pattern with the skips:

4 skip to 6  - skip 1
9 skip to 13 - skip 4
20 skip to 28 - skip 8
43 skip to 59 - skip 16

What's going on?

Upvotes: 0

Views: 312

Answers (1)

MamaWalter
MamaWalter

Reputation: 2113

Maybe an answer, it could be a side effect of a new algorithm called “consecutive“ for the innodb_autoinc_lock_mode - Source

Upvotes: 1

Related Questions