Search code examples
mysqlperformanceprimary-keyauto-increment

MySQL performance with missing values on autoincrement id as key

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?

Solution

  • 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.