我有一个 jquery 滚轮/滚动器，它显示从我的“有用提示”数据库表查询返回的记录片段。我想让滚动条保持在大约 15 条记录，但不总是前 15 条记录。
编写这样的查询是否更有效：
SELECT *
FROM table
ORDER BY RAND()
LIMIT n
哪个返回随机结果，还是返回整个查询并让我的 ColdFusion 组件提供查询结果的随机数？
我的滚动条的未来还将包括“待售物品”表中的随机记录，因此我需要牢记这一点。
I've got a jquery roller/scroller that displays snippets of records returned from my 'Helpful Hints' database table query. I want to keep the scroller to about 15 records but not always the first 15 records.
Is it more efficient to write a query like:
SELECT *
FROM table
ORDER BY RAND()
LIMIT n
Which returns a random result or do I return the whole query and have my ColdFusion component serve up a random number of the query result?
The future of my scroller will include random records from my 'Items for Sale' table as well, so I need to keep that in mind.
发布评论
评论（3）
我同意这一点，因为返回大量不用于 Coldfusion 的值结果集是一种永远无法收回的资源浪费。
但要小心使用 MySQL 的 RAND( ) 排序功能 - 一旦记录超过 100,000 条，它就无法很好地扩展 (参见图表），您应该看看使用替代方案。
I agree with bemace that returning a large result set of values that won't be used to Coldfusion is a waste of resources that can never be recouped.
But be careful about using MySQL's RAND() function for ordering - once you're over 100,000 records, it doesn't scale well (see graph), and you should look at using alternatives.
除非您要缓存相对较小的静态查询，否则在数据库端随机化记录通常会更有效。这样每次只提取 15 条记录，而不是表中的所有记录。
Unless you are caching a relatively small and static query, it is usually more efficient to randomize the records on the database side. That way you are only pulling 15 records each time, not all records in the table.
返回整个结果集并扔掉大部分结果肯定会降低效率。
当您需要包含其他表中的数据时，您可以运行另一个查询（与您已有的查询类似），然后在 ColdFusion 中将结果混合在一起。
为了完整起见，类似这样的事情是可能的，尽管速度太慢而不实用：
Returning the entire result set and them throwing most of it out would definitely be less efficient.
When you get to the point of including data from other tables you can run another query like the one you've got and then shuffle the results together in ColdFusion.
For the sake of completeness, something like this is possible, though too slow to be practical: