mysql randomizing result and optimization

Question

I want to have randomized rows after a query, but using order by rand() is just exhausting on a table that has 120k+ rows. I have found a small solution that just outputs number of rows but it runs like it starts from a random index and then returns #number of rows after that. It is pretty fast but this just returns some rows after a random index. The code goes like:

SELECT *
FROM lieky AS r1 JOIN 
   (SELECT (RAND() *
                 (SELECT MAX(col_0)
                    FROM lieky)) AS id)
    AS r2
WHERE r1.col_0 >= r2.id
ORDER BY r1.col_0 ASC
LIMIT 100

and i found it in here: http://jan.kneschke.de/projects/mysql/order-by-rand/

Is there something that would help me ?

I am trying to get randomized data into pagination, so when the user queries the database, he will always get the rows in a random order. Thanks for help.

Dmitry Dmitry · Accepted Answer · 2013-07-16T16:54:19

It should be noted that

(SELECT (RAND() * (SELECT MAX(col_0) FROM lieky)) AS id)

can return MAX(col_0), so you ll get only 1 row (because of WHERE r1.col_0 >= r2.id)

I think good solution should be somethink like:

add two columns groupId int, seed int; add index indexName (groupId , seed)
every x seconds (maybe every hour, day, ..) run script that ll be recalc these columns (see below)
when user open your rows list first time (or when you want to re-rand items) you save any random groupId to user's session; groupId can be from 0 to (select max(groupId) from lieky)
to show rows you use query like: (select * from lieky where groupId=%saved groupId% order by Seed limit x,100) — it should be very fast

About recalc script, it ll rather slow (so it's good idea to run it at night).
Seed you can update by using:

update lieky set Seed = rand()*1000000

Then set GroupId=0 for first N rows, GroupId=1 for following N rows, ...
N is max rows that you can show for user (max_page)*(per_page_count)

mysql randomizing result and optimization

1 Answers