MySQL performance with missing values on autoincrement id as key

Question

Example: I have a tab "books" with "id" (int autoincrement) as primary key, name, author, price etc...(not important). There's 5 books and I delete the book with id=3. When I add other books the autoincrement value will start from 6 and there's a missed 3 in the id sequence (1 2 4 5 6). So multiple delete/add can create a tab with missing id values if I don't set the autoincrement value or I don't reassign id to books.

Can the situation with missing numbers in id create lowering of performance in queries?

score 2 · Accepted Answer · answered Jan 18 '13 at 15:05

2

You don't need to worry about performance with missing auto-increment values.

Just make sure you choose the proper data type for the auto-increment column so you do not run out of values.

answered Jan 18 '13 at 15:05

Ike Walker

64,401
14
110
109

score 0 · Answer 2 · answered Jan 18 '13 at 15:18

0

Thats okay with auto-increment. A normal integer field would do the job for a very big amount of data. You could use "unsigned" option to increase this amount.

answered Jan 18 '13 at 15:18

Zeal

239
1
3

I know about int and unsinged. I needed info on performances. Thanks anyway. – CalCon Jan 18 '13 at 18:01

MySQL performance with missing values on autoincrement id as key

2 Answers2