-- 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.