ORDER BY RAND()替代 [英] ORDER BY RAND() alternative

问题描述

可能重复:
MySQL:ORDER BY RAND()的替代方法

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

我目前有一个以ORDER BY RAND(HOUR(NOW())) LIMIT 40结尾的查询，以获取40个随机结果.结果列表每小时更改一次.

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.

您能否建议一种获取随机(ish)结果集的替代方法，该结果会不时发生变化?它不必每小时每小时，也不必完全随机.

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

推荐答案

我认为更好的方法是将产品标识符下载到中间层，在需要时(每小时或每个请求一次)随机选择40个值并使用他们在查询中:product_id in (@id_1, @id_2, ..., @id_40).

I think the better way is to download product identifiers to your middle layer, choose random 40 values when you need (once per hour or for every request) and use them in the query: product_id in (@id_1, @id_2, ..., @id_40).

这篇关于ORDER BY RAND()替代的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！