random selection from mysql avoiding deleted records

[showman13]

I know there are a lot of articles about random numbers, but can't find an efficient way to do what I need.

if I have a table that is designed with an auto increment ID for each record, it would be pretty simple to get the min and the max and then get a random number between them.

But if over time, some of those records have been deleted, the more that are deleted, the more chance that I'll come up with a random number for a record that doesn't exist.

Is there an efficient way to do the random number selection so that it only returns IDs that do in fact exist in the table?

And something that will work equally as well on 100 records in the table or 100,000 records.?

thanks in advance for your feedback.

Douglas

[pixelsoul]

I don't know what backend language you're using but, you could try pulling all records that are not marked as deleted and then select a random one from the results in the array.

[showman13]

Well, that is one option, but if there are a few hundred thousand records in it, that could be rather cumbersome.

I use PHP...

[Traevel]

Get a random number between 0 and the amount of records using count x rand, then use LIMIT offset, amount.

So for instance, let's say the random number is 5.

SELECT * FROM foo LIMIT 5,1

would give you the 6th row (starts at 0). You could also just select the ID if you need it.

On a table with 1.1M records:

 SELECT CAST((COUNT(*)*RAND())-1 AS UNSIGNED) FROM `person`
 Query took 0.1211 sec

 SELECT * FROM `person` LIMIT 521541, 1
 Query took 0.1884 sec

Managable I'd reckon.

[pixelsoul]

That's a good point Traevel. I believe this would work as well.

SELECT column FROM table
ORDER BY RAND()
LIMIT 1

[Traevel]

It does yes, but at a larger cost.

To compare:

SELECT * FROM `person` ORDER BY RAND() LIMIT 1
Query took 1.1831 sec

It's still managable I suppose, depending on how often you need to use it.

[showman13]

Thank you for your responses, but none of them really indicate any efficiency from what I can tell.

Here is one I snatched from one of my numerous web searches on various keywords... I'll keep searching and if I find anything faster will let you know what it is.

It isn't extremely efficient, but supposedly 16 times faster thatn just the ORDER BY RAND() on by itself.

And it works great... at least on my small test table.

$sql = "
  SELECT *
  FROM members
  WHERE RAND()<(SELECT ((1/COUNT(*))*10) FROM members)
  ORDER BY RAND()
  LIMIT 1
";
  $result = mysql_query($sql);
  $rand_row = mysql_fetch_row($result) ;
  print $rand_row[0]; // prints the member ID just for testing.

[showman13]

Quick follow up question on that select...

I wouldn't use select *, but would define the fields to retrieve, but is there a quick and easy way to increment a counter for that record in the process of selecting it that would keep track of how many times it has been selected?

Just an after thought actually, but would be good to do.

thanks
Douglas

[showman13]

Traevel,

I must apologize... I just read your first post and realized that I missed reading part of it...

Those numbers do actually indicate a more efficient method than the ORDER BY RAND()

Guess I was speed reading and not paying close enough attention.

Thank you again.
Douglas

[Traevel]

No worries

is there a quick and easy way to increment a counter for that record in the process of selecting it that would keep track of how many times it has been selected?

The easy way would be to store the amount of selections in that table (or a separate one). However, if you don't use the amount of previous selections during the selection process it would only serve a statistical use.

[showman13]

What I was referring to was simply putting a field in the table that would be more or less a simple counter of how many times each record was accessed.

This is basically an internal banner display for our members, and would just give me the ability to show the member how many times their individual banner was displayed.

I know that when I select the random record, I can get the ID and then do a seperate query to update the counter, but it seems to me that there is a way to update it at the same time that it is being selected...

Anyway, thanks again for your responses.
Douglas