[PHP+MySQL] Holes in primary key auto_increment column

[VigilPHP]

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?

By holes i mean:

 1     NAME     EMAIL     ETC
 2     NAME     EMAIL     ETC
 4     NAME     EMAIL     ETC
 8     NAME     EMAIL     ETC
90    NAME     EMAIL     ETC
91    NAME     EMAIL     ETC
92    NAME     EMAIL     ETC
93    NAME     EMAIL     ETC
94    NAME     EMAIL     ETC
.
.
.

What if say the random number is 5, but there is no 5. Thanks in advance.

[r937]

there is nothing wrong with holes

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

but re-sequencing the column is a poor solution

[VigilPHP]

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.

I'll wait it out to see if there are other suggestions as well.

[VigilPHP]

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?

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?

[r937]

try this --

WHERE id >= $rand LIMIT 1

since id is the primary key, it is indexed, and therefore this query is efficient

note that this will not appear very random if there are ~huge~ gaps -- but normal deletions are okay