[thelist] MySQL queries and AUTO_INCREMENT

[sbeam]

On Friday 07 April 2006 01:40 pm, Matt Warden wrote:
> > I agree with John Brooking: Use a separate sequence field, and 
update
> > it. Or, if the number of records will be not too large, use ORDER BY
> > RAND() LIMIT 1.
> 
> I would suggest this either way. I'm not sure issuing 3 queries over
> the network is more efficient than using this method (although, I
> haven't done any testing).

Just curious - as an alternative - would there be any downside to just 
using a result set pointer/cursor - something like

$res = $db->query("SELECT id, file_name FROM photos");
$pic = $res->fetchRow(DB_FETCHMODE_ASSOC, rand(1, $res->numRows());
$res->free();

(Using PHP/PEAR because thats just what I'm used to. You could do the 
same thing lots of ways)

So you are only doing one rand() in the application code and not 10^4 
rand's in SQL plus a sort.


-- 

# S Beam - Web App Dev Servs
# http://www.onsetcorps.net/