If you have been doing LAMP (or anything MySQL) related for a while, you already know that the simplest way of getting a random set of rows from a table by using ORDER BY RAND() is a really Bad IdeaTM. The guts of SQL require that this solution generate a random number for each of the posts in your result set and then sort by this. Random numbers are generally expensive, and this requires one be made for each row, which as your result set grows will become quite the bottleneck.
I have seen other alternatives to this:
- Pull all of the IDs from the table and then select X number of random ones in PHP and run another query to pull those random ones – http://www.webtrenches.com/post.cfm/avoid-rand-in-mysql
- Use a slightly more complex set of SQL variables – http://www.electrictoolbox.com/msyql-alternative-order-by-rand/
I am testing out another approach that I’m calling “modulus” sorting. The ID approach is decent, but as tables get large, you end up doing a large data transfer to get all of those IDs. My solution may not be truly random, but it appears to be doing nicely so far.
First query:
$query = “SELECT COUNT(*) AS c FROM `table`”;
Second query:
$query = “SELECT * FROM `table` ORDER BY `id` % ” . mt_rand(0, $count / 2);
Essentially, we pull the number of records in the table and assume that the IDs are fairly evenly distributed and then sort them by the modulus of their ID and a random value. The modulus operator is quite a bit faster than RAND() and the results I have been getting so far are random enough for my purposes. I’ll drop in updates as we see how it goes!
