Thursday, January 26, 2006

An optimized alternative to sorting by rand()

Earlier I asked the question about how can one use the functionality of rand() in an efficient way to sort the results.

Scott Noyes pointed out a post by Jan Kneschke that answered my question.

The query I created after going through Jan's post is given below for quick reference:

SELECT url,field1,field2 FROM active AS active1 JOIN ( SELECT ROUND( RAND( ) * ( SELECT MAX( cid ) FROM active ) ) AS cid) AS active2 WHERE active1.cid >= active2.cid ORDER BY active1.cid ASC LIMIT 1


1 comment:

magician said...

I find if you need a greater subset of data, ie. 5 results, they just come out increasing. Is there a way to tweak this to get N random rows pulled and still be efficient?