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?

Answer 1

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