-2

When you have a high number of ids, but they aren't used anymore. Should you reset the id of the table? And does it have any performance difference?

2
  • What do you mean? What is "reset"? Remove column? alter values to least and adjacent? something else? does it have any performance difference? If you do not change its datatype - no, of course.
    – Akina
    Apr 14 at 12:31
  • which database ? Apr 14 at 12:53
1

Short answer: It's probably not worth the effort.

If you mean that your auto-incremented values are "very high" and you are considering resetting the auto-increment value back to, say, zero, then maybe.

The most important consideration is that you don't run out of or attempt to duplicate values.
As to the numerical value of each one? Doesn't matter a jot. Whilst, to you or me, the Integer value 4,294,967,293 "looks" bigger than 12, the DBMS sees them exactly the same - four bytes of Data with "stuff" in them.
The risk you run by resetting the auto-increment value is that if you were to get a rush of new records being added, you might "catch-up" with the older records with larger id's - and then your application would start breaking all over the place.

Alternatively, if you mean that all your records id's are "very high" and you want to re-map them all to smaller values?
Do not do this.
Here's an analogy as to why not. A bunch of people close their accounts at your bank. Would you expect your checking account number to be "resequenced" to some smaller value as a result? Of course not. There would be [historical] confusion about who had which account number at which point in time and the chances of things (i.e. money!) "crossing-over" into the wrong accounts during the switchover(s)? Not worth thinking about.

Changing primary (and, of course, foreign) key values in a database is this scale of operation - not to be done lightly, or even at all.

Not the answer you're looking for? Browse other questions tagged or ask your own question.