MySQL: Alternatives to ORDER BY RAND()

user_221736 0
Viewed 102723

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
sql-order-by random mysql sql
9 Answers
SELECT
    a.id,
    mod_question AS modQuestion,
    mod_answers AS modAnswers 
FROM
    b_ask_material AS a
    INNER JOIN ( SELECT id FROM b_ask_material WHERE industry = 2 ORDER BY RAND( ) LIMIT 100 ) AS b ON a.id = b.id
user_127535680

I had the same issue today, I fixed it by using limit and offset You can do by iterating 18 times over a random set of offsets

  • to avoid duplicates, you can create your random set of offset like this in python sample(range(1, rows_count), random_rows_count)
  • then for each offset get the corresponding row using OFFSET and LIMIT 1 and add it to a list
  • rows_count can be cached to avoid performance issue to count total number of rows at each request

EDIT it's actually what this post https://stackoverflow.com/a/40398306/3045926 proposes

user_30459260
Related