php randomly select records from database

Question

Possible Duplicate:
MySQL select 10 random rows from 600K rows fast

I have a table with over 300,000 records. I need to select 10 or 13 or 20 records from this table.

I have tried following ways.

This takes long time to select

SELECT * FROM products ORDER BY RAND() LIMIT 0,12

OR

This way same thing but i can't seem to select more then 1 record

$temp = mysql_query('SELECT id FROM products limit 0,12');
if ( count( $temp ) > 0 ) {
    $j = 1;
    foreach( $temp as $index => $row ) {
        $p[$j++] = $row[id];
    }
    $my_p= $p[ rand( 1, --$j ) ];
    $pp = 'SELECT id FROM products WHERE id = {$my_p}';
}

UPDATE: Following this MySQL select 10 random rows from 600K rows fast

I have

    SELECT * FROM QM_Products AS r1 
JOIN (SELECT (RAND() * (SELECT MAX(id) 
FROM QM_Products )) AS id) AS r2 WHERE r1.id >= r2.id 
ORDER BY r1.id ASC 
LIMIT 0, 10

This works Thank you all.

Look at the sidebar. Multiple questions about the same issue. — DCoder, Dec 25 '12 at 17:33

score 1 · Answer 1 · answered Dec 25 '12 at 17:29

1

Try this php Code

$r = mysql_query("SELECT count(*) FROM user");  
$d = mysql_fetch_row($r);  
$rand = mt_rand(0,$d[0] - 1);  

$r = mysql_query("SELECT username FROM user LIMIT $rand, 1");

answered Dec 25 '12 at 17:29

Muhammad Raheel

19,823
7
67
103

I'm getting same records. – user1509201 Dec 25 '12 at 17:39

score 1 · Accepted Answer · answered Dec 25 '12 at 18:22

You chose to use this solution:

SELECT * FROM QM_Products AS r1 
JOIN (SELECT (RAND() * (SELECT MAX(id) 
FROM QM_Products )) AS id) AS r2 WHERE r1.id >= r2.id 
ORDER BY r1.id ASC 
LIMIT 0, 10

However, I will point out that you could get fewer than 10 rows, if the random expression returns an id close to the end of the table.

Also, this isn't much of a random selection, because the rows returned will always have adjacent id values.

Aside from the ORDER BY RAND() solution (of course this doesn't perform well on large datasets because it can't use an index), I can think of another way to select a number of rows randomly. Here's some pseudo-PHP code:

// MAX(id) is quicker than COUNT(*) in InnoDB
$maxid = query "SELECT MAX(id) FROM QM_Products"

// generate a list of N random values, making sure they're distinct
$id_list = array();
while (count($idlist) < 10) {
  $rand_id = mt_rand(1,$maxid);
  $id_list[$rand_id] = 1;
}

// convert the id list to a comma-separated string in an SQL query
$id_list = implode(",", array_keys($id_list)); 
$rows = query "SELECT * FROM QM_Products WHERE id IN ($id_list)"

You might get fewer than N rows if the random values hit gaps where no row exists with a matching id. But the above method is much faster than ORDER BY RAND() or issuing N queries for 1 row at a time. So you could design a loop to repeat the search as many times as necessary to find N rows. This wouldn't be good if you have very large gaps, because it could loop for a long time before finding enough rows.

php randomly select records from database

2 Answers2