Good alternative to Order By Rand() to get Random values in MYSQL

Quite often people use queries like:

SELECT quote FROM quotes ORDER BY RAND() LIMIT 1

to get a random row (rows) from a table. That’s quite bad idea. For big tables, if your table have just 50-100 rows, use whatever you want. What happens when you run such a query?

Let’s say you run this query on a table with 10000 rows, than the SQL server generates 10000 random numbers, scans this numbers for the smallest one and gives you this row. Generating random numbers is relatively expensive operation, scaning them for the lowest one (if you have LIMIT 10, it will need to find 10 smallest numbers) is also not so fast (if quote is text it’s slower, if it’s something with fixed size it is faster, maybe because of need to create temporary table).

So, I was facing similar problem with a table of over 100K entries and found a really useful answer on Stackoverflow. I can't upvote that answer to sharing it with everyone here :

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