Page 1 of 1

auto_increment and scalability issue

Posted: Mon Oct 16, 2006 4:07 pm
by konstandinos
hello folk

i have a table that stores images (although this problem applies to any entity). one of the attributes is image_id. i assign this to be the primary key as well as auto_increment.

now over time (years) many images will be uploaded, and many shall be deleted. however i've noticed that if i add 3 images, the id gets assigned automatically as 1, 2 and 3 respectively. if i delete the row corresponding to id 2, and then add another row after that, the new row doesn't get id 2, but id 4.

thus, over time, 10 000 images in the database for example, thousands of those id's in between won't be used. how do i avoid this? surely it's a waste of space otherwise?

thanks

Posted: Mon Oct 16, 2006 4:11 pm
by feyd
It's uses no more space than any other number. They are intended to be unique over all time irrespective of deletions. Would it be desirable that if an id was reused and someone clicked an old link to the previous image that the new image shows? Generally, the answer is no. If your answer is yes, then you don't need/wany an auto-incremented field.