Optimize MySQL random when using ordering by two parameters

Question

I need to get some data from tables and order them by two parameters. One of the parameters are RAND() because records needs to be random. I have large database and using RAND() slows down performance alot and I want to avoid it. Is there any chance to optimize performance of random ordering ? I need to order my result based on count (lowest to highest) and then I need to perform ordering by RANDOM.

This is query that I am using

SELECT c.username, IF(s.Broj IS NULL,0,s.Broj) as Ukupno
FROM user AS c
LEFT JOIN (
  SELECT username, COUNT(*) AS Broj
  FROM odis
  GROUP BY username
) AS s
ON c.username = s.username
ORDER BY s.broj ASC, RAND()

Are you sure that `rand()` is slowing down the query and not the `order by`? — Gordon Linoff, Mar 23 '15 at 11:08
ordering by RAND() is slowing my query @GordonLinoff . when I remove rand from ordering it goes well — jureispro, Mar 23 '15 at 11:11
possible duplicate of [How can i optimize MySQL's ORDER BY RAND() function?](http://stackoverflow.com/questions/1244555/how-can-i-optimize-mysqls-order-by-rand-function) — Marcus Adams, Mar 23 '15 at 11:44
Please provide `EXPLAIN SELECT ...` for the query with and without the `RAND()`. I, too, am puzzled by why RAND is the villain. — Rick James, Mar 23 '15 at 22:48

score 0 · Answer 1 · answered Mar 23 '15 at 11:12

0

I actually doubt very much that the rand() is slowing down the query much more than the order by would. But, you can put the rand() in the subquery, which should do the same thing:

SELECT c.username, coalesce(s.Broj, 0) as Ukupno
FROM user c LEFT JOIN
     (SELECT username, COUNT(*) AS Broj, rand() as r
      FROM odis
      GROUP BY username
     ) s
     ON c.username = s.username
ORDER BY Ukupno ASC, r

Note that r will have NULL values when there is no match. For this group, the ordering may not be as random as you would like.

answered Mar 23 '15 at 11:12

Gordon Linoff

1,242,037
58
646
786

Thank you for your answer. When I remove ordering by RAND my query runs 100 times faster . on 200 records, without rand query runs for 0.0003 seconds. But when I use rand execution time is 0.0435 seconds . That's only 200 records. What if there's a 10x times more rows ... – jureispro Mar 23 '15 at 11:24
1

0.0003 seconds smells like it came from the Query cache. Please try it again, but with SELECT **SQL_NO_CACHE** ... – Rick James Mar 23 '15 at 22:49
@RickJames . . . Or it sounds like `s.broj` has an index. – Gordon Linoff Mar 24 '15 at 01:12

Optimize MySQL random when using ordering by two parameters

1 Answers1