mysql auto increment primary key running out

Question

I maintain a table with an ID AUTO INCREMENT PRIMARY KEY. When I delete an entry and re-add one, the new entry does not take the ID of the previous one instead it increments again by one. Is that normal and is it advised to not change this behavior? I just have a feeling this is creating a non scalable system as eventually it could run out of indexes.

Answer 1

This is by design, million of databases have primary keys like these with an integer key.

If you delete 90% of your inserts, you will run out of keys after 400 million rows¹⁾
If and when you do you can do an

ALTER TABLE `test`.`table1` MODIFY COLUMN `item_id` BIGINT UNSIGNED NOT NULL
, ROW_FORMAT = DYNAMIC;

where column item_id would be your primary key.
After that you'll never have to worry about running out of key-space again.

Don't be tempted to start out with a bigint primary key!

1. It will make all your queries slower.
2. It will make the tables bigger.
3. On InnoDB the primary key is included on every secondary index, making a small primary key much faster with inserts.
4. For most tables you will never need it.

If you know your big table will have more rows than the integer can hold, than by all means make it a bigint, but you should only do this for tables that really need it. Especially on InnoDB tables.

Don't use a GUID, it's just a lot of wasted space, slowing everything way down for no reason 99,99% of the time.

---

¹⁾ using a unsigned! integer as primary key.

Answer 2

User niceguy07 uploads a picture of his kitten. The picture is saved as 000012334.jpg because you use primary keys as filenames instead of putting untrusted user data into them (which is a good idea).

niceguy07 sends a link with ?picture_id=12334 to his date.

niceguy07 deletes his kitten pictures and user fatperv08 uploads a picture of himself wearing only a batman mask.

Your database reuses primary keys, so unfortulately now the link with ?picture_id=12334 points to a picture of a naked fat perv wearing a batman mask.

Re-using primary key values of deleted records is an extremely bad idea. It is, in fact, a bug, if the primary key leaks out of the database because you use it in :

• a URL
• a filename
• dumped along with other data in a file
• etc

Since it is, in fact, very useful to do all of the above, not reusing primary key ids is a good idea...