In a MariaDB database I’ve one specific table where the id (auto_increment) value must be as correlative as possible to minimize ID gaps .
The current solution is based on sending the command “ALTER TABLE xxx AUTO_INCREMENT = 1” after each delete, that on InnoDB tables resets the counter to the maximum next available value.
Although this works I’m not sure that this kind of solution can be considered a good practice, so I would like to know what the DB experts here think about.
Why would you do that?
Just leave the values increase forever.
Doing this would mean it needs linear scan through all records every single time you insert.
The command is issued only after a record delete in this table, not in INSERT/UPDATES.
And this table has -and will ever have- only few daily operations, just about 50, so may me the rescan performance impact is negligible.
The point in this case is avoid ID gaps as much as possible.
Why avoid those?
They don’t do harm.
And if the ID has been used for another table to reference back, reusing the ID may put a new record in the relationship to old records.
This seems like a very bad idea. Let the database handle the recordID as it sees fit. Forcing it to do your bidding like this will most likely result in bad things happening.
And if you really have special requirements for what ever reasons. Then you should not use auto number at all. Usual way for such would then to have Sequence management table where you store whatever hints are needed to be stored for your sequence and you then use that to generate primary key.
Good sequence manager could also store hint for last ID that was returned.
There may be valid reasons for such custom sequences but such should never ever be confused or mixed with Database auto-numbering scheme.
Not a good practice. It should be avoid as hell if you care about performance.
Usually we don’t want to “reuse” some already used ID in the past. Specially if such code was registered somewhere, like a report.
Thanks fof the inputs, guys, my suspects have been confirmed.
These kind of ids should have no meaning in the business logic of your database. The users of an auto incremented id be they a developer or a business user, should be oblivious as to the actual value. If the value starts to have meaning it’s time for a rethink.
In a DB design review, I’m always suspicious of tables having an auto increment PK and no candidate key.