RAND() Query and Performance

Question

I'm trying to prepare a query for performance. I'm hoping to remove the RAND() from the query below and replace it with a better performing alternative. Does anybody have any suggestions?

SELECT video.*, 
       video.wins / video.loses AS win_loss_ratio
  FROM video
 WHERE video.videoid NOT IN (SELECT vidlog.videoid
                               FROM video AS vid, 
                                    video_log AS vidlog
                              WHERE vid.videoid = vidlog.videoid)
   AND video.round = 0
ORDER BY RAND(), win_loss_ratio DESC 
LIMIT 0, 2

Thanks!

possible duplicate of [MySQL: Alternatives to ORDER BY RAND()](http://stackoverflow.com/questions/1823306/mysql-alternatives-to-order-by-rand) — OMG Ponies, Mar 26 '11 at 20:25
thanks for pointing this link out. I did some searching prior to the post; however, i was interested in opinions on my entire query. — Tegan Snyder, Mar 26 '11 at 22:31

Gary Green · Accepted Answer · 2011-03-26T20:30:25.463

1

Instead of using the RAND() use a random number in the offset for the LIMIT in the language that is calling this query. That could have two problems though a. you need to know how many items are in the database, 2. only yield something random for one of the items though. Other option is to drop the LIMIT and RAND() altogether and just select random items when the query is returned (I would imagine this to be slower though)

Is there a real reason why you need to have this very high in performance? I'm pretty sure using RAND() will be sufficient enough for this kind of query.

edited Mar 26 '11 at 20:30

answered Mar 26 '11 at 20:25

Gary Green

22,045
6
49
75

1

The performance of `ORDER BY RAND()` is dependent on the number of records being handled. – OMG Ponies Mar 26 '11 at 20:34
I'm already returning a count of the records prior to selecting two random records. I could potentially use this as the basis. – Tegan Snyder Mar 26 '11 at 22:30

score 0 · Answer 2 · answered Mar 26 '11 at 20:29

0

Instead of the in subquery, try an exclusive left join? That way, MySQL knows it doesn't have to scan the video table twice:

SELECT  video.* 
,       video.wins / video.loses AS win_loss_ratio
FROM    video
LEFT JOIN    
        video_log as vidlog
ON      vid.videoid = vidlog.videoid
WHERE   vidlog.videoid is null
        AND video.round =0
ORDER BY 
        RAND()
LIMIT   0 , 2

answered Mar 26 '11 at 20:29

Andomar

232,371
49
380
404

Depends on if the `VIDEO_LOG.videoid` can be NULL -- if it can, `NOT EXISTS` is a better choice. Otherwise, `LEFT JOIN/IS NULL` is the best choice (looks like a foreign key, so likely this is the best choice). But that doesn't address the performance issue regarding using `ORDER BY RAND()`, which begins at 100+K records. – OMG Ponies Mar 26 '11 at 20:33
video_log will be null on certain occasions. what kind of performance issues will begin when i hit 100k records? – Tegan Snyder Mar 26 '11 at 22:29

RAND() Query and Performance

2 Answers2

Linked