MySQL: Shuffle a limited query result? [duplicate]

This question already has answers here: Closed 12 years ago.

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?

---

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

---

SELECT user FROM users
ORDER BY RAND()
LIMIT 5