Optimizing My MySQL Statement! - Rand() Too Slow

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.

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.

How do I optimize my RAND() function for large tables?

The performance problem with your query is not the rand() per se but the order by on a large number of rows. If you know that foo has a certain number of rows, say 1,000,000, then something like this will run much faster:

SELECT *
FROM foo
WHERE RAND() < 0.00001
ORDER BY RAND()
LIMIT 20;

The where clause reduces the number of rows for the order by to about 100. And, you would be very confident that the number would be at least 20. You can automate this calculation as well:

SELECT *
FROM foo cross join
     (SELECT count(*) as cnt FROM foo) const
WHERE RAND() < 100.0/cnt
ORDER BY RAND()
LIMIT 20;

ORDER BY RAND() function taking long time to execute in mysql

I go the solution.

 SELECT p1.ID, p1.post_content, p1.post_title, p1.post_date, p1.post_name
     FROM posts as p1 JOIN
          (SELECT CEIL(RAND() *
                 (SELECT MAX(ID)
                    FROM posts)) AS id)
          AS p2
  WHERE p1.ID >= p2.id
  ORDER BY p1.ID ASC
  LIMIT 0, 24

This is faster than my query.

MySQL select 10 random rows from 600K rows fast

Here is the solution.

Thanks

query order by rand() too slow

I think your problem is that your query requires a full table scan for the WHERE clause. The order by does make things worse -- depending on the volume that pass the filter.

You might consider storing this number in the table and adding an index to it:

alter table offers add column start_to_price float;

update offers
    set start_to_price = start_price / price;

create index idx_offers_s2p on offers(start_to_price);

Then, your query might be fast:

SELECT o.*
FROM `offers` o 
WHERE start_to_price >= 2
ORDER BY RAND()
LIMIT 1;

If performance is still a problem, then I would be likely to use a where clause first:

SELECT o.*
FROM `offers` o CROSS JOIN
     (select COUNT(*) as cnt from offers where start_to_price >= 2) oo
WHERE rand() <= 10 / cnt
ORDER BY RAND()
LIMIT 1;

This pulls about 10 rows at random and then chooses one of them.

If these don't work, then there are other solutions that get progressively more complicated.

---

Related Topics