Page 1 of 1

random rows question

Posted: Wed Jan 20, 2010 10:00 pm
by jeffz2010
How to select eg. 20 random rows from a big table (10.000 - 100.000 rows)?
RAND() is way to slow.

additional table info:
1. empty rows, e.g.: 401, 402, ?, 404
2. there are two types of rows - one should be used for random rows selection, another type should be excluded.
I thought of this - but have doubts:
If it was not for #2, I would go for php random numbers generation and used them in IN(random numbers).

What I'm afraid of is that e.g. I select 20 random numbers and some will be from these rows, which are excluded, I'm in trouble, as I cannot select enough rows.

Same may happen if I select 50 random numbers and 40 of them will be from excluded rows, or even all of them.
Has anyone come across some good way to handle that?
Something that works for you well?

Re: random rows question

Posted: Wed Jan 20, 2010 11:57 pm
by jeffz2010
It does not seem like php generated random numbers outperforms "ORDER BY RAND()"

I select from table with ca. 130.000 records/rows.
Ratio of eligible, not eligible (or empty) rows is more or less 40/60%.
Expected return: not less than 75 qualified rows.

Parse times I'm getting: 0.035s to 0.082s

Code: Select all

 
    $select = 0;
    do {
            $select+=25;
            
            $max_query("select MAX(orders_total_id) as id from TABLE");
            $max = $max_query;
            $arr = array_rand(range(1,$max['id']), $select);
            $arr = array_unique($arr);
        
            $in_string = '';
            $ct = 0;
            foreach ($arr as $val)
                {
                    $ct++;
                    $pref = '';
                    if ($ct>1) $pref = ', ';
                    $in_string .= $pref.$val;
                }
                
            $res_query("select valA, valB, valC from TABLE where valD IN (".$in_string.") AND valA != 0 AND  valB != ''");
            $row = 0;
            $c++;
            while ($res = $res_query)
                {
                    $row++;
                    $temp_array[] = $row.$res ['valA'].$res ['valB'].$res ['valC'];
                }
                
            $temp_array = array_unique($temp_array);
    
        } while (count($temp_array) < 75);