Friendlier alternative to rand()

Marvin · 03-29-2008, 04:40 PM

Hey all, wondering if you could offer some advice to someone with very little experience!

I am displaying some pictures from different albums with the following:

PHP Code:


			
$lpictures = $db->query("
  SELECT albumpicture.*, album.*, user.username, user.usergroupid,
  IF(displaygroupid=0, user.usergroupid, displaygroupid) AS displaygroupid 
  FROM " . TABLE_PREFIX . "albumpicture AS albumpicture
  LEFT JOIN " . TABLE_PREFIX . "album AS album ON(album.albumid = albumpicture.albumid)
  LEFT JOIN " . TABLE_PREFIX . "user AS user ON(user.userid = album.userid)
  WHERE album.state = 'public' 
  ORDER BY dateline DESC LIMIT 0, $displayrecords  
  ");

This displays the pictures in terms of date, the last x pictures uploaded to the gallery. I want to display a random selection of pictures.

I achieved this by changing

PHP Code:


			
ORDER BY dateline DESC LIMIT 0, $displayrecords

PHP Code:


			
ORDER BY rand() DESC LIMIT 0, $displayrecords

Using rand() seems to be frowned upon by developers, and a total dead-end when you try to use it on a large table. I clearly want anything to run as smoothly as possible, but don't know any alternatives besides rand() maybe you could help me out?

Thanks!

Geert · 03-30-2008, 10:53 AM

You may want to read this article: ~jk ORDER BY RAND()

It gets rather complex, but if you look all the way at the bottom at the performance you win, it may be worthwhile.

Marvin · 03-30-2008, 12:49 PM

Thanks Geert, that has thrown me in at the deep end a little - but i'll have a close re-read and try and get to grips with it.

Anyone else feel free to share your input too.