Is there something better than ORDER BY RAND in a query?

[Boofo]

I have been researching this on the net and ORDER BY RAND is supposed to be bad to use. I can across another way to get a random item from the db, but it uses a sub-query. From what I have read, this query is faster and less server intensive on bigger tables.

$quote=$db->query_first("
SELECT quote,name
FROM " . TABLE_PREFIX . "quote
WHERE quoteid = ROUND(".lcg_value()." * (SELECT COUNT(*) FROM " . TABLE_PREFIX . "quote))
LIMIT 1
");


This is the code that I was using before:

$quote=$db->query_first("
SELECT quote,name
FROM " . TABLE_PREFIX . "quote
ORDER BY RAND(NOW())
LIMIT 1
");


My quotes table is getting bigger all the time as I add quotes to it. Is the top query (with the sub-query) actually better than the old RAND query? Or is there an even better way to do it now?

[Andreas]

If you are using MyISAM tables the first query should indeed be faster.

Just EXPLAIN tham and you'll see :)

[Boofo]

So, this is one of those rare times where 2 queries are actually better than one. ;)

From what I have read on it, it is supposed to be even more random than the ORDER BY RAND(). But what would this do against a table that has missing IDs or empty rows?

--------------- Added 1274156362 at 1274156362 ---------------

Andreas,

I just ran the EXPLAIN for the queries and this is what I got:

lcg_value
Results: 2 (0.0001s)
1 PRIMARY - Impossible WHERE noticed after reading const tables
2 SUBQUERY - Select tables optimized away


ORDER BY RAND
Results: 1 (0.0001s)
1 SIMPLE - (table)quote - (type)ALL - (rows)1912 - Using temporary; Using filesort

I don't understand what the lcg_value info means. Is it bad or good?

[Boofo]

Andreas,

I found out that the new query does not handle empty rows/missing IDs very well. It will show a blank quote. It only happened once on a 2210 quote table that had 5 missing rows. The rows are now all filled in so it shouldn't happen again.