MySQL: Shuffle a limited query result?

Question

Possible Duplicate:
Simple Random Samples from a (My)Sql database

Hi!

Let say that I have a table of users. I want a query that shuffle the result and only show 5 users. How do I do that in MySQL query without using any php?

Answer 1

You can use rand(), but the performance is terrible

select * from users order by rand() limit 5; <-- slow

I would suggest, store list of all user id into an serialize array and cache into a disk-file. (periodically update)

So, you can un-serialize it back using PHP, and use PHP array_rand to pick 5 random users.

To fetch the full information, you can do

select * from users where user_id in(...); <-- very fast

Answer 2

SELECT user FROM users
ORDER BY RAND()
LIMIT 5