Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

MySQL: Shuffle a limited query result? [duplicate]

Tags:

sql

mysql

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?

like image 296
einstein Avatar asked Dec 26 '10 14:12

einstein


People also ask

How to Avoid a cross join?

Ways to avoid performance issues associated with CROSS JOINs: Use another JOIN (INNER/LEFT/RIGHT) with 2 ON conditions. Use the GROUP BY clause to pre-aggregate data.

How to Avoid cartesian product SQL?

To avoid a Cartesian product, you must specify how the tables should be combined. Typically, you want to pair rows based on matching values in one or more key columns of each table.


1 Answers

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
like image 52
ajreal Avatar answered Oct 12 '22 23:10

ajreal