Page 1 of 1
Defragmenting the id column?
Posted: Wed Sep 03, 2008 4:47 am
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
?
Re: Defragmenting the id column?
Posted: Wed Sep 03, 2008 5:10 am
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;
Re: Defragmenting the id column?
Posted: Wed Sep 03, 2008 5:45 am
by Sindarin
Won't there be any problem if I don't do it? Like array getting out of range or conflicts?
Re: Defragmenting the id column?
Posted: Wed Sep 03, 2008 5:56 am
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.

Re: Defragmenting the id column?
Posted: Wed Sep 03, 2008 6:39 am
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.

Oh yeah! That's lifetime guarantee!

I think yes, I am using bigint for the id column.
Re: Defragmenting the id column?
Posted: Wed Sep 03, 2008 8:21 am
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).
Re: Defragmenting the id column?
Posted: Mon Sep 08, 2008 3:48 pm
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?
Re: Defragmenting the id column?
Posted: Mon Sep 08, 2008 4:32 pm
by onion2k
You can, yeah.
Code: Select all
ALTER TABLE `table` AUTO_INCREMENT = 10;