So, I want to pull a query on a MySQL database, however, I want the result to be randomized - that is, I don't want the same thing to come up in the same order every time. This is because I will only be listing the first 6 items of the query (or 2 items, in some cases), and I want all the contents of the database to have a chance to appear.
Is it possible to do this in MySQL or would I have to use PHP to do it?
For a fast approach try this:
SELECT *
FROM (
SELECT @cnt := COUNT(*) + 1,
@lim := 10
FROM t_random
) vars
STRAIGHT_JOIN
(
SELECT r.*,
@lim := @lim - 1
FROM t_random r
WHERE (@cnt := @cnt - 1)
AND RAND(20090301) < @lim / @cnt
) i
See this article for a detailed description of how it works:
A simpler (but slow way) is to use ORDER BY RAND():
SELECT *
FROM yourtable
ORDER BY RAND()
LIMIT 6
From the manual:
... you can retrieve rows in random order like this:
mysql> SELECT * FROM tbl_name ORDER BY RAND();ORDER BY RAND() combined with LIMIT is useful for selecting a random sample from a set of rows:
mysql> SELECT * FROM table1, table2 WHERE a=b AND c<d -> ORDER BY RAND() LIMIT 1000;RAND() is not meant to be a perfect random generator. It is a fast way to generate random numbers on demand that is portable between platforms for the same MySQL version.
If you have a unique id field which is incrementing from 1 to n without any gaps you can improve performance even more by choosing six random numbers in [1, n] and fetching the six rows with those ids. This avoids scanning the full table.
Mark Byers is correct this has been asked before.
You want to put your random algorithm at the application layer vs the database layer. There is a huge performance hit at the database layer (assuming MySQL). Doing it at the application layer will involve some extra coding but you will find it is much more flexible as you can update the algorithm to suit your latest business needs, not to mention you can implement optimizations such as caching.
