Defragmenting the id column?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
Sindarin
Forum Regular
Posts: 521
Joined: Tue Sep 25, 2007 8:36 am
Location: Greece

Defragmenting the id column?

Post by Sindarin »

In my CMS system when I delete an entry, with that entry missing the id column (auto incremement) will possibly look like:

1
2
3
5
6
7
8

That is id 4 missing now. Is there any way to defragment the id column and make it be again like,

1
2
3
4
5
6
7

?
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Re: Defragmenting the id column?

Post by onion2k »

Don't do it. The whole point of an auto-incrementing column is that the numbers are used once. If you update them then any references that use the id as a key will break.

That's not to say you can't do it, it's actually very easy..

Code: Select all

SET @counter := 0;
 
UPDATE `table` 
SET
`id` = ( SELECT @counter := @counter +1 )
WHERE 1;
User avatar
Sindarin
Forum Regular
Posts: 521
Joined: Tue Sep 25, 2007 8:36 am
Location: Greece

Re: Defragmenting the id column?

Post by Sindarin »

Won't there be any problem if I don't do it? Like array getting out of range or conflicts?
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Re: Defragmenting the id column?

Post by onion2k »

Well, in theory you could run out of numbers, but you'd need to have inserted 4,294,967,295 things into the database (assuming your id column is an unsigned INT). If you inserted 1 thing a second it'd take you a little over 136 years to fill the table up. I wouldn't worry about it.

If you're really worried about it then make the column an unsigned BIGINT ... then you can have 18,446,744,073,709,551,615 records before you run out of numbers. That's 5.85 × 10^11 years at 1 thing a second. I think that may be longer than the universe has existed for. ;)
User avatar
Sindarin
Forum Regular
Posts: 521
Joined: Tue Sep 25, 2007 8:36 am
Location: Greece

Re: Defragmenting the id column?

Post by Sindarin »

That's 5.85 × 10^11 years at 1 thing a second. I think that may be longer than the universe has existed for.
:D Oh yeah! That's lifetime guarantee! :drunk: I think yes, I am using bigint for the id column.
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Re: Defragmenting the id column?

Post by onion2k »

BIGINT is a little unnecessary really. Unless you have a specific reason for needing numbers that large it's better to use INT ... it'll take less space and it'll actually be a bit faster (unless you're on a 64bit processor in which case it'll be the same).
User avatar
Sindarin
Forum Regular
Posts: 521
Joined: Tue Sep 25, 2007 8:36 am
Location: Greece

Re: Defragmenting the id column?

Post by Sindarin »

onio2k I tried your sql query. Although entries have indeed being defragmented, the number keeps incrementing from where it left though. Like I now have 1,2,3,4,5,6,7 but a new entry will be 96,97,98... Can't I reset this?
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Re: Defragmenting the id column?

Post by onion2k »

You can, yeah.

Code: Select all

ALTER TABLE `table` AUTO_INCREMENT = 10;
Post Reply