MySQL: Alternatives to ORDER BY RAND()

Question

I've read about a few alternatives to MySQL's ORDER BY RAND() function, but most of the alternatives apply only to where on a single random result is needed.

Does anyone have any idea how to optimize a query that returns multiple random results, such as this:

   SELECT u.id, 
          p.photo 
     FROM users u, profiles p 
    WHERE p.memberid = u.id 
      AND p.photo != '' 
      AND (u.ownership=1 OR u.stamp=1) 
 ORDER BY RAND() 
    LIMIT 18

Answer 1

Here's an alternative, but it is still based on using RAND():

  SELECT u.id, 
         p.photo,
         ROUND(RAND() * x.m_id) 'rand_ind'
    FROM users u, 
         profiles p,
         (SELECT MAX(t.id) 'm_id'
            FROM USERS t) x
   WHERE p.memberid = u.id 
     AND p.photo != '' 
     AND (u.ownership=1 OR u.stamp=1) 
ORDER BY rand_ind
   LIMIT 18

This is slightly more complex, but gave a better distribution of random_ind values:

  SELECT u.id, 
         p.photo,
         FLOOR(1 + RAND() * x.m_id) 'rand_ind'
    FROM users u, 
         profiles p,
         (SELECT MAX(t.id) - 1 'm_id'
            FROM USERS t) x
   WHERE p.memberid = u.id 
     AND p.photo != '' 
     AND (u.ownership=1 OR u.stamp=1) 
ORDER BY rand_ind
   LIMIT 18

Answer 2

I wrote an article about a solution about a year go:

http://devzone.zend.com/article/4571-Fetching-multiple-random-rows-from-a-database