6

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.

7

For MySQL you can do

ORDER BY RAND()

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

6

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.

  • Good tip randomizing the nid with php. – googletorp Jun 16 '11 at 8:48
  • @googletorp, thanks (personally I would still aim to do it on database side with CREATE FUNCTION from dev.mysql.com/doc/refman/5.1/en/create-procedure.html unless it would be possible to create a random nid from the php side without additional round-trips to the database) – Unreason Jun 16 '11 at 10:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.