auto_increment and scalability issue

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
konstandinos
Forum Commoner
Posts: 68
Joined: Wed Oct 04, 2006 4:20 am

auto_increment and scalability issue

Post 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
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.
Post Reply