auto_increment and scalability issue
Posted: Mon Oct 16, 2006 4:07 pm
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
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