Random records **faster** than ORDER BY RAND()

-- ORDER BY RAND() is very very slow way of selecting random numbers -- This I set a limit to at 25 because I have filters in the WHERE -- which cause less than 25 records to get pulled back from the random -- batch that get pulled. Not all of my records returned are active -- and have images. I use this to rotate out of over 26,000 products -- on a live site in just .14 seconds on VPS which doesn't have much more -- than 1GB of RAM. I'm about to make it into a function which populates -- a table for daily use and just use a garbage collector instead of -- every hit to the index page itself running an extra 10th of a second. -- SELECT * is faster than naming individual columns on my installation. SELECT * FROM ( SELECT @cnt := COUNT(*) + 1, @lim := 25 FROM `products` ) AS `vars` STRAIGHT_JOIN ( SELECT `r`.*, @lim := @lim - 1 FROM `products` AS `r` WHERE ( @cnt := @cnt - 1) AND RAND() < @lim / @cnt ) AS `i` WHERE `active` = 1 AND `image` != '' ORDER BY `product_id` DESC;

Be the first to comment

You can use [html][/html], [css][/css], [php][/php] and more to embed the code. Urls are automatically hyperlinked. Line breaks and paragraphs are automatically generated.