select two random rows in MySQL database

Question

I have a database full of images, and I want to spit out and display two random images. This code does it properly, but I'm not convinced that it's the best way to do it, especially if the database will eventually have many many rows. I've looked into using MySQL's rand() function and limiting it to two results, but from what I've read rand() is relatively slow on large databases. Another issue is in the double database query. Is there a better way to select two random rows by img_id?

img_id is an auto_incremented row but cannot be assumed to be continuous.

//get all image ids
$query = $conn->prepare('SELECT img_id FROM images');
$query->execute();
$result = $query->fetchAll();

//create an array in which to put all the ids
$list_imgs = array();

//put the ids into the array to search over
for ($x=0; $x < count($result); $x++) {
    array_push($list_imgs, $result[$x]['img_id']);
}

//output two random images
for ($x=0; $x < 2; $x++) {
    //create random index for search
    $rand = array_rand($list_imgs);

    //query to select one image
    $query = $conn->prepare('SELECT title, file_loc FROM images WHERE img_id=?');
    //random index value in array of img_ids
    $query->execute(array($list_imgs[$rand]));
    $result = $query->fetchAll();

    echo 'title:' . $result[0]['file_loc'] . '<br /><img src="' . $result[0]['file_loc'] . '" />';
}

any suggestions to make the query more efficient?

Have you tested one query with `order by rand()` against the 3 queries you have now? By the way, you can make your current code more efficient by moving the `prepare` statement out of the loop, you only need to prepare once. — jeroen, Dec 18 '12 at 18:45
And you can select two random rows directly from `$result`, no need to generate a new array with the same values... — jeroen, Dec 18 '12 at 18:49

score 6 · Accepted Answer · edited May 23 '17 at 11:47

6

you could use

SELECT img_id, title, file_loc FROM images order by rand() limit 2

so you'd end up with

$query = $conn->prepare('SELECT img_id, title, file_loc FROM images order by rand() limit 2');
$query->execute();
$result = $query->fetchAll();

foreach($result as $row) {
    echo 'title:' . $row['file_loc'] . '<br /><img src="' . $row['file_loc'] . '" />';
}

Note that order by rand() can be especially slow on large tables. See How can i optimize MySQL's ORDER BY RAND() function? for ways to optimize it

edited May 23 '17 at 11:47

Community

1
1

answered Dec 18 '12 at 18:45

DiverseAndRemote.com

19,314
10
61
70

Although I've never tested it myself, I've heard that rand() is slow on large tables so I'm hesitant to use it. Do you know if there is any validity to this concern? – Hat Dec 18 '12 at 18:50
My answer has a link in it to ways to optimize for large data sets – DiverseAndRemote.com Dec 18 '12 at 18:55

score 0 · Answer 2 · answered Dec 18 '12 at 18:48

0

Use a script that is executed every x (your call) to mark two pictures to be shown.

answered Dec 18 '12 at 18:48

dualed

10,262
1
26
29

score 0 · Answer 3 · answered Dec 18 '12 at 18:49

0

Not as sure about this with MySQL, in MS SQL I would do:

SELECT TOP 2 img_id, newid() FROM images ORDER BY newid()

If it works similary in MySQL it would be

SELECT img_id, uuid() FROM images ORDER BY uuid() LIMIT 2

answered Dec 18 '12 at 18:49

Luke

156
5

score 0 · Answer 4 · answered Dec 18 '12 at 18:50

selecting all images at first is overkill ..

you can do someting like this:

 SELECT file_loc
 FROM random AS r1 JOIN
       (SELECT (RAND() *
                     (SELECT MAX(id)
                        FROM random)) AS id)
        AS r2
 WHERE r1.id >= r2.id
 ORDER BY r1.id ASC
 LIMIT 2

you can check this article:http://jan.kneschke.de/projects/mysql/order-by-rand/

score 0 · Answer 5 · answered Aug 25 '16 at 12:48

0

select * from table order by rand() limit 0,2

Code taken from here: http://chandreshrana.blogspot.in/2014/06/how-to-fetch-randomly-two-records-from.html

answered Aug 25 '16 at 12:48

Chandresh

361
2
7

select two random rows in MySQL database

5 Answers5