The SitePoint Forums have moved.

You can now find them here.
This forum is now closed to new posts, but you can browse existing content.
You can find out more information about the move and how to open a new account (if necessary) here.
If you get stuck you can get support by emailing forums@sitepoint.com

If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

[PHP+MySQL] Holes in primary key auto_increment column

Hi,

Right now the website is to randomly pick a row with a "ID" number from the table, the number is chosen via php. The problem is there can be holes within the numbers due to deletion of entries over a period of time.

So far I had the table to drop the ID field and re-add it as auto_increment primary key to recount the numbers every time some entry is deleted. However the number of entries in row has surpassed 10000 and the slowness is notable.

recently few people even accessed pages while its recounting even, its not quite efficient. Is there anyway for mysql to easily recount and order the numbers accordingly?

the purpose of an auto_increment column is to provide uniqueness, nothing more

if the random number generated is 5, but there is no row with id=5, then you can do several things -- generate another random number until you get a hit, retrieve the first row with an id that is greater than 5, or generate the random number on some other column

so you are suggesting its just better to choose a random number and loop it through while until i get a random one that does exist? ^_^'

That is okay, but in situations where I need 6 -10 random numbers and their corresponding 6-10 rows that's going to be a lot of while loops and queries.... is that really much efficient than resorting the ID field?

Its worth a try for sure but it would have been nice if it could be resorted though. Thanks r937.

Right now was trying to use "WHERE `ID`='$rand_num'.. but holes in sequence are the problem. The main thing is i'm avoiding order by RAND()... So I was trying on my thinking cap and got an idea. Wouldn't the following work out for me without any load on the server?

Code:

SELECT * FROM `TABLE` WHERE `ETC`='something' LIMIT $rand_num, 1

I mean the LIMIT row, entries.. the row doesn't matter if its sequenced or not, it doesn't change does it?