Rails: faster way to get N random records

Question

It is well documented how to quickly extract a single record or how not to get multiple records inefficiently (plucking all identifiers). I am wondering what is the fastest way to get N records from a table of millions of records.

I found with a MariaDB table with a 3M row, selecting all identifiers takes 10+ seconds, and rand () orders took a minute. This makes me think that N individual random offset calls (after finding the total number of tables) can be faster if we assume that N is relatively small. I'm still wondering if there is a faster way or not, some sort of trick to make random offsets in a single request.

Answer 1

I recently had to read and think about it! The best solution I came across is the one given in http://explainextended.com/2009/03/01/selecting-random-rows/ : create a random function that can be applied iteratively, along the lines along lines of this request from the page (for n = 10):

SELECT  rnd_id, rnd_value
FROM    (
        SELECT  @cnt := COUNT(*) + 1,
                @lim := 10
        FROM    t_random_innodb
        ) vars
STRAIGHT_JOIN
        (
        SELECT  r.*,
                @lim := @lim - 1
        FROM    t_random_innodb r
        WHERE   (@cnt := @cnt - 1)
                AND RAND() < @lim / @cnt
        ) i

Please note that you cannot use this approach if you have additional conditions, and I do not think that there is a particularly effective way to solve it in this case, reliably getting the required number of results.