CODEWITHSUNDEEP
phpmysql
Hat
Hat

Reputation: 1731

select two random rows in MySQL database

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?

Upvotes: 5

Views: 4062

Answers (5)

Chandresh
Chandresh

Reputation: 371

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

Upvotes: 0

DiverseAndRemote.com
DiverseAndRemote.com

Reputation: 19888

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

Upvotes: 7

d.raev
d.raev

Reputation: 9546

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/

Upvotes: 0

Luke
Luke

Reputation: 156

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

Upvotes: 0

dualed
dualed

Reputation: 10502

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

Upvotes: 0

Related Questions