How Can I Optimize MySQL's ORDER BY RAND() for Better Performance?

Optimizing MySQL's ORDER BY RAND() Performance

When optimizing MySQL queries, addressing slow queries containing the ORDER BY RAND() function can be challenging. This article explores effective solutions to enhance the performance of such queries.

A common approach is to use a subquery that calculates the rank of each row based on a random value. This allows for efficient selection of random rows without requiring actual sorting.

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 method is particularly efficient for MyISAM tables, but offers significant performance improvements in InnoDB as well.

For selecting a single random record, an alternative approach can be used:

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 approach assumes a relatively even distribution of ac_id values. By utilizing these techniques, you can significantly optimize MySQL queries involving ORDER BY RAND().

The above is the detailed content of How Can I Optimize MySQL's ORDER BY RAND() for Better Performance?. For more information, please follow other related articles on the PHP Chinese website!