MySQL: ORDER BY RAND() – a case study of alternatives

I finally had the time to translate my post from german to english – so here we go 🙂

Das Problem

Many of you reading this post might already stopped by this problem – you just want a few random rows from your database and most of the times you might have used „ORDER BY RAND()“ and not thought about, what’s happening there. Just add all the columns you want to select, add JOINS, WHERE, etc. and add ORDER BY RAND() and LIMIT.

For small applications this might even scale well, but what happens in large applications with hundreds, thousands or millions of rows in a table?

I want to look a little closer into this. While developing my deprecated rhCMS (a content management system), I asked myself the same question.