使用不带:total_entries的will_paginate可以改善冗长的查询 [英] Using will_paginate without :total_entries to improve a lengthy query
问题描述
我有一个 will_paginate 的当前实现，该实现使用 paginate_by_sql 方法来构建要分页的集合.我们有一个针对 total_entries 的自定义查询，该查询非常复杂，给数据库造成了很大的负担.因此，我们希望从分页中完全删除total_entries.
I have a current implementation of will_paginate that uses the paginate_by_sql method to build the collection to be paginated. We have a custom query for total_entries that's very complicated and puts a large load on our DB. Therefore we would like to cut total_entries from the pagination altogether.
换句话说，除了典型的分页显示上一个1 [2] 3 4 5下一个"外，我们只希望使用下一个-上一个"按钮.但是我们需要知道一些事情.
In other words, instead of the typical pagination display of 'previous 1 [2] 3 4 5 next', we would simply like a 'next - previous' button only. But we need to know a few things.
- 我们是否显示上一个链接?当然，只有在当前选择中显示的记录之前存在记录的情况下，才会发生这种情况
- 我们是否显示下一个链接?如果正在显示集合中的最后一条记录，则不会显示
从文档
用于计数行的查询将 如果您自动生成 不提供:total_entries.如果你 遇到问题 生成的SQL，您可能想要 在您的笔记本中手动执行计数 应用程序.
A query for counting rows will automatically be generated if you don’t supply :total_entries. If you experience problems with this generated SQL, you might want to perform the count manually in your application.
所以最终理想的情况是以下情况.
So ultimately the ideal situation is the following.
- 删除total_entries计数，因为它导致数据库负载过多
- 使用下一个/上一个按钮进行导航，一次显示50条记录，无需分页显示
- 仅显示相应的下一个按钮和上一个按钮
有人遇到过类似的问题或对解决方案有想法吗?
Has anyone worked with a similar issue or have thoughts on a resolution?
推荐答案
在很多情况下，will_paginate会在计算条目数方面做得非常糟糕，尤其是当涉及到的联接使count SQL生成器感到困惑时.
There are many occasions where will_paginate does a really awful job of calculating the number of entries, especially if there are joins involved that confuse the count SQL generator.
如果您需要的只是一个简单的prev/next方法，那么您所要做的就是尝试从数据库中检索N + 1个条目，并且仅获得N个或少于最后一页的N + 1个条目.
If all you need is a simple prev/next method, then all you need to do is attempt to retrieve N+1 entries from the database, and if you only get N or less than you're on the last page.
例如:
per_page = 10
page = 2
@entries = Thing.with_some_scope.find(:all, :limit => per_page + 1, :offset => (page - 1) * per_page)
@next_page = @entries.slice!(per_page, 1)
@prev_page = page > 1
您可以轻松地将其封装在某些模块中，该模块可以包含在需要该模块的各种模型中，也可以进行控制器扩展.
You can easily encapsulate this in some module that can be included in the various models that require it, or make a controller extension.
我发现这比默认的will_paginate方法要好得多.
I've found that this works significantly better than the default will_paginate method.
唯一的性能问题是MySQL的局限性，具体取决于表的大小.
The only performance issue is a limitation of MySQL that may be a problem depending on the size of your tables.
无论出于何种原因，在MySQL中以较小的LIMIT执行查询所花费的时间与OFFSET成正比.实际上，数据库引擎会通读所有导致特定偏移值的行，然后返回下一个LIMIT number行，而不是像您期望的那样向前跳过.
For whatever reason, the amount of time it takes to perform a query with a small LIMIT in MySQL is proportional to the OFFSET. In effect, the database engine reads through all rows leading up to the particular offset value, then returns the next LIMIT number rows, not skipping ahead as you'd expect.
对于大型数据集，您的OFFSET值在100,000以上的范围内，您可能会发现性能显着下降.这种显示方式是，加载第1页的速度非常快，第1000页的速度有些慢，而第2000页的速度非常慢.
For large data-sets, where you're having OFFSET values in the 100,000 plus range, you may find performance degrades significantly. How this will manifest is that loading page 1 is very fast, page 1000 is somewhat slow, but page 2000 is extremely slow.
这篇关于使用不带:total_entries的will_paginate可以改善冗长的查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
