• Home
  • Engineering
  • Business
  • Travel

DeMar.is

DeMar.is

Monthly Archives: July 2012

Modulus Sorting – Alternative to ORDER BY RAND()

04 Wednesday Jul 2012

Posted by Justin DeMaris in Engineering

≈ Leave a comment

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!

Subscribe

  • Entries (RSS)
  • Comments (RSS)

Archives

  • April 2015
  • March 2015
  • July 2012
  • June 2012
  • January 2012
  • December 2011
  • November 2011
  • March 2010
  • January 2009
  • July 2008
  • March 2008
  • February 2008
  • January 2008
  • August 2007
  • June 2007
  • May 2007
  • April 2007
  • February 2007
  • January 2007
  • November 2006
  • June 2006
  • February 2006
  • January 2006
  • December 2005
  • November 2005
  • October 2005
  • July 2005
  • June 2005

Categories

  • Business
  • Engineering
  • Travel
  • Uncategorized

Meta

  • Register
  • Log in

Blog at WordPress.com.

  • Follow Following
    • DeMar.is
    • Already have a WordPress.com account? Log in now.
    • DeMar.is
    • Customize
    • Follow Following
    • Sign up
    • Log in
    • Report this content
    • View site in Reader
    • Manage subscriptions
    • Collapse this bar