NYCPHP Meetup

[Dan Cech]

Jeff Siegel wrote:
> I'll definitely use the LIMIT clause. After all, I only need one record. 
> In addition, I'll have some parameters in a where clause so that if it 
> comes back with a previously used record (I'm tracking that info)...I'll 
> rerun/regenerate the query. Probably just do it in a loop and then exit 
> out of the loop once I've found an unused record.

Another method would be to add an extra column to the table to track the 
number of times each record has been used, then you could do the following:

SELECT count(*) FROM mytable WHERE times_used=0;

then use that result in a call to mt_rand to get a random offset:

$random_offset = mt_rand (0, $max - 1);

and grab the row with:

$row = SELECT * FROM mytable WHERE times_used=0 LIMIT $random_offset, 1;

Then update the table with the id from the row:

UPDATE mytable SET times_used=times_used+1 WHERE id_column=$id;

Honestly I can't tell you if there is an advantage to this method over 
the simple:

SELECT * FROM mytable WHERE times_used=0 ORDER BY RAND() LIMIT 1;

but I would be very interested to see how it worked out, especially with 
regards to how each solution scaled with the number of rows in the table.

Note: for either of these solutions to be efficient the times_used 
column would have to be indexed.

Dan

> 
> Jeff
> -----
> 
> David Sklar wrote:
> 
>> Ophir Prusak wrote:
>>
>>>
>>> I've never seen this solution before. Very simple.
>>> I wondering though about the efficiency of this solution.
>>>
>>> It seems to me that this assigns a random value to each row and then 
>>> orders by that row.
>>> Does the DB do a full table scan using this method?
>>>
>>> If yes, this would be a very inefficient method for very large tables.
>>
>>
>>
>> It is inefficient if you are retrieving the whole table ordered by 
>> RAND(), but with the LIMIT clause, the table scan is short-circuited 
>> after one row is found. So,
>>
>> SELECT * FROM table ORDER BY RAND()
>>
>> can be slow if there are a lot of rows in the table, but
>>
>> SELECT * FROM table ORDER BY RAND() LIMIT 1
>>
>> is OK.
>>
>> David
>>
>>
>>
>> _______________________________________________
>> talk mailing list
>> talk at lists.nyphp.org
>> http://lists.nyphp.org/mailman/listinfo/talk
>>
> 
> _______________________________________________
> talk mailing list
> talk at lists.nyphp.org
> http://lists.nyphp.org/mailman/listinfo/talk
>