Sunday, August 2, 2009

Common mistake: looking for "random" rows

There are many wrong ways to find a "random" row. For example:


The rand() function is great, and LIMIT is phenomenal, but the above query is really, really bad. It evaluates the rand() function for every row of the table and then (if that wasn't bad enough) it orders all the rows by the result of the rand function. Then it returns the first row. Wow. Don't make MySQL do that much work!

There are better ways to find a "random" row. Maybe in a future blog post I'll go into them, but at the very least consider calculating a random number ahead of time and looking up a row with that id.

1 comment:

  1. Jan wrote a good post on it quite a while ago: