Ok,
select * from table ORDER BY RAND() limit 1
is unacceptable because it seems to evaluate (and randomly shuffle) EVERY row before returning a result.
This is of course too slow and unacceptable. So I've been tinkering with some other ideas. I'm trying REALLY hard to get this done elegantly and in a single query.
I've tried a few of these without success:
select * from table where id=(mod(floor(rand()*4294967296),max(id))+1);
select *,floor(rand()*4294967296) as randnum,max(id) as maxid from table where id=(mod(randnum,maxid)+1);
And then tried removing rand() thinking it was screwing up the max() function:
select * from table where id=(mod(1231231,max(id))+1);
Most of my experimentation seems to return:
ERROR 1111: Invalid use of group function
Any insight?
Thanks for the help,
Steve
select * from table ORDER BY RAND() limit 1
is unacceptable because it seems to evaluate (and randomly shuffle) EVERY row before returning a result.
This is of course too slow and unacceptable. So I've been tinkering with some other ideas. I'm trying REALLY hard to get this done elegantly and in a single query.
I've tried a few of these without success:
select * from table where id=(mod(floor(rand()*4294967296),max(id))+1);
select *,floor(rand()*4294967296) as randnum,max(id) as maxid from table where id=(mod(randnum,maxid)+1);
And then tried removing rand() thinking it was screwing up the max() function:
select * from table where id=(mod(1231231,max(id))+1);
Most of my experimentation seems to return:
ERROR 1111: Invalid use of group function
Any insight?
Thanks for the help,
Steve