Never use ORDER BY RAND() again!

Posted on March 5, 2008. Filed under: mysql+, PHP | Tags: , , , , , |

I have been guilty of using ORDER BY RAND() in MySQL queries to return random records from time to time, but everyone knows to avoid it like the plague. For those who agree with me, aside from using it on very small tables [1 – 500 records], I have an elegant workaround using PHP.


<?php
    $condition = true;
    while ($condition) {
        $randID = rand(1,$totalRecordsInTblExample);
        $r = mysql_query("SELECT * FROM `tblexample` WHERE `exampleID`=$randID LIMIT 1");
        if (mysql_num_rows($r) == 1) {
            $condition = false;
        }
    }
    $exampleTitle = mysql_result($r,0,"exampleTitle");
?>

The very first thing we need to know is how many records there are within the table in question, if the table is MyISAM, you’re in luck. Getting the total number of records is quick and inexpensive since MyISAM keeps an index of how many records a given table contains:


<?php
    $r = mysql_query("SELECT COUNT(*) AS `count` FROM `tblexample`");
    $numRecords = mysql_result($r,0,"count");
?>

However, don’t attempt this on an InnoDB table! InnoDB would need to count each and every row every time, which would be expensive on large tables. I keep a reference of record counts in a MyISAM table [for stats purposes], so I query that table to get the record count instead.

Once we have determined how many records are in the table, we can use PHP to [duh] select a random number between 1 and the total number of records.

Once we have that random number, query the DB to see if the record exists, and if not try again. Since the column in the where clause is the Primary Key [I assume], the lookups will be very fast and cheap.

Advertisements
Read Full Post | Make a Comment ( 19 so far )

Liked it here?
Why not try sites on the blogroll...