Randomize the result of a database query

Question

I'm executing a query for which I want to randomize the results.

I tried with $res=db_query("SELECT DISTINCT nid FROM content_type_event ORDER BY RANDOM()"), but this doesn't work.

Answer 1

For MySQL you can do

ORDER BY RAND()

To order by random, for PgSQL RANDOM() instead should yield the desired result.

Answer 2

googletorp's answer is correct, I can only offer some notes:

• Keep in mind that this approach will be relatively heavy on the database - ordering by RAND() will have to build temporary structures to sort all of the records for each time you execute this query. Therefore this approach should be used if you really do need to sort all of the records randomly. If you are getting only one random record (or a few) then much more efficient approach would be to generate a random nid and select on it (where existing index can be used; there are different strategies on this - you might still do it on the database side, or you might do it from the application; query execution time might be different by two orders of magnitude).

• Next the DISTINCT might slow things down further as it also have to build temporary index to guarantee DISTINCT values; here you might be better of by selecting from nodes and just checking if a record EXISTS in content_type_event (but YMMV here)

As always, it is good to have a few variants and test on real data.