ORDER BY RAND() alternative [duplicate]

Question

Possible Duplicate:
MySQL: Alternatives to ORDER BY RAND()

I currently have a query that ends ORDER BY RAND(HOUR(NOW())) LIMIT 40 to get 40 random results. The list of results changes each hour.

This kills the query cache, which is damaging performance.

Can you suggest an alternative way of getting a random(ish) set of results that changes from time to time? It does not have to be every hour and it does not have to be totally random.

I would prefer a random result, rather than sorting on an arbitrary field in the table, but I will do that as a last resort...

(this is a list of new products that I want to shuffle around a bit every now and then).

Answer 1

If you have an ID column it's better to do a:

-- create a variable to hold the random number
SET @rownum := SELECT count(*) FROM table;
SET @row := (SELECT CEIL((rand() * @rownum));

-- use the random number to select on the id column
SELECT * from tablle WHERE id = @row;

The logic of selecting the random id number can be move to the application level.

SELECT * FROM table ORDER BY RAND LIMIT 40

is very inefficient because MySQL will process ALL the records in the table performing a full table scan on all the rows, order them randomly.

Answer 2

Its going to kill the cache because you are expecting a different result set each time. There is no way that you can cache a random set of values. If you want to cache a group of results, cache a large random set of values, and then within sub sections of the time that you are going to use those values do a random grab within the smaller set [outside of sql].