MySQL: Alternatives to Order by Rand()

ORDER BY RAND() alternative

I think the better way is to download product identifiers to your middle layer, choose random 40 values when you need (once per hour or for every request) and use them in the query: product_id in (@id_1, @id_2, ..., @id_40).

mysql order by rand() performance issue and solution

First of, all generate a random value from 1 to MAX(id), not 100000000.

Then there are at least a couple of good solutions:

1. Use > not =

   SELECT items FROM tablea where status='0' and id>'$id23' LIMIT 1
   

   Create an index on (status,id,items) to make this an index-only query.

2. Use =, but just try again with a different random value if you don't find a hit. Sometimes it will take several tries, but often it will take only one try. The = should be faster since it can use the primary key. And if it's faster and gets it in one try 90% of the time, that could make up for the other 10% of the time when it takes more than one try. Depends on how many gaps you have in your id values.

mysql heavy RAND query alternatives

From MySQL document:

SELECT * FROM tablename ORDER BY RAND() LIMIT 1

works for small tables, but once the tables grow larger than 300,000 records or so this will be very slow because MySQL will have to process ALL the entries from the table, order them randomly and then return the first row of the ordered result, and this sorting takes long time. Instead you can do it like this (atleast if you have an auto_increment PK):

SELECT MIN(id), MAX(id) FROM tablename;

Fetch the result into $a

//php code

$id=rand($a[0],$a[1]);

SELECT * FROM tablename WHERE id>='$id' LIMIT 1

Why don't use mysql ORDER BY RAND()?

The problem of ORDER BY RAND() is that as your explain tells you the "Using temporary" and the "Using filesort". For each request a temporary table is created and sorted. Thats a pretty heavy operation. It will probably not matter when your database is not under heavy load but it will cost a lot of performance.

SQL order by RAND()

ORDER BY RAND() copies the whole table into a temporary table and adds a new column with a random value. Finally, it sorts the data by that column.

This of course, has an impact on performance, so it's not recommended.

For more information:

http://www.roberthartung.de/mysql-order-by-rand-a-case-study-of-alternatives/

http://wanderr.com/jay/order-by-slow/2008/01/30/

https://bugs.mysql.com/bug.php?id=65177

How can i optimize MySQL's ORDER BY RAND() function?

Try this:

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

This is especially efficient on MyISAM (since the COUNT(*) is instant), but even in InnoDB it's 10 times more efficient than ORDER BY RAND().

The main idea here is that we don't sort, but instead keep two variables and calculate the running probability of a row to be selected on the current step.

See this article in my blog for more detail:

• Selecting random rows

Update:

If you need to select but a single random record, try this:

SELECT  aco.*
FROM    (
        SELECT  minid + FLOOR((maxid - minid) * RAND()) AS randid
        FROM    (
                SELECT  MAX(ac_id) AS maxid, MIN(ac_id) AS minid
                FROM    accomodation
                ) q
        ) q2
JOIN    accomodation aco
ON      aco.ac_id =
        COALESCE
        (
        (
        SELECT  accomodation.ac_id
        FROM    accomodation
        WHERE   ac_id > randid
                AND ac_status != 'draft'
                AND ac_images != 'b:0;'
                AND NOT EXISTS
                (
                SELECT  NULL
                FROM    accomodation_category
                WHERE   acat_id = ac_category
                        AND acat_slug = 'vendeglatohely'
                )
        ORDER BY
                ac_id
        LIMIT   1
        ),
        (
        SELECT  accomodation.ac_id
        FROM    accomodation
        WHERE   ac_status != 'draft'
                AND ac_images != 'b:0;'
                AND NOT EXISTS
                (
                SELECT  NULL
                FROM    accomodation_category
                WHERE   acat_id = ac_category
                        AND acat_slug = 'vendeglatohely'
                )
        ORDER BY
                ac_id
        LIMIT   1
        )
        )

This assumes your ac_id's are distributed more or less evenly.

---

Related Topics