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

Optimization of MySQL's ORDER BY RAND() Function

Introduction

The use of the ORDER BY RAND() function in MySQL can result in slow query performance, particularly in poorly optimized tables. This article delves into efficient techniques for optimizing such queries and provides practical solutions to resolve this issue.

Optimization Techniques

One effective solution involves the use of a subquery combined with STRAIGHT_JOIN, as demonstrated below:

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 has proven particularly efficient for MyISAM tables due to the instantaneous nature of the COUNT(*) operation. For InnoDB tables, it remains significantly faster than ORDER BY RAND().

In cases where only a single random record is required, a different 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 method assumes a relatively even distribution of ac_id values.

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