Getting random values in mySql query

Question

I have a query which selects 6 names in the database. eg

SELECT names FROM users LIMIT 6

Now I want this query to select a random 6 names in the database table, is this possible? And how

Answer 1

The simple but slow solution is:

SELECT names FROM users ORDER BY RAND() LIMIT 6

This uses sorting on a random number and has O(n log n) performace. It should run fine for say 10000 rows, but for larger tables it won't scale well.

To get it faster you can look at Quassnoi's article MySQL: selecting a number of random rows fast.

SELECT  *
FROM    (
        SELECT  @cnt := COUNT(*) + 1,
                @lim := 6
        FROM    users
        ) vars
STRAIGHT_JOIN
        (
        SELECT  names,
                @lim := @lim - 1
        FROM    users r
        WHERE   (@cnt := @cnt - 1)
                AND RAND() < @lim / @cnt
        ) i

This has O(n) performance.

Answer 2

SELECT names 
FROM users 
ORDER BY RAND() 
LIMIT 6 

Answer 3

Here is a other quick solution

Select names from users WHERE RAND() LIMIT 6