List:General Discussion« Previous MessageNext Message »
From:Nuno Pereira Date:July 21 2005 10:03am
Subject:Re: random rows selection
View as plain text  
Michael Monashev wrote:

 > How to select 5 random rows from big table with WHERE clause?

Maybe something like this:
SELECT col1, col2 FROM table WHERE col3=123 AND (id_col=RAND() OR 
id_col=RAND() OR id_col=RAND() OR id_col=RAND() OR id_col=RAND())

the problem is that there is the (low) probability of 2 equal id_cols 
get randomly generated, return only 4 different rows

 > This query very slow on 1 mln rows:
 > SELECT col1, col2 FROM table WHERE col3=123 ORDER BY RAND() LIMIT 5

but that problem can also occur in your slow query

Note: I have already sent this privatly forgeting to sen do the list

-- 
Airconditioners and computers have one thing in common: Once you open 
windows everything fails.

Nuno Pereira
Thread
random rows selectionMichael Monashev20 Jul
  • Re: random rows selectionGleb Paharenko20 Jul
    • Re: random rows selectionMichael Monashev20 Jul
      • Re: random rows selectionGleb Paharenko21 Jul
  • Re: random rows selectionChris Knipe20 Jul
  • Re: random rows selectionNuno Pereira21 Jul