如何在postgresql上删除或更改cakephp分页计数查询? [英] How can you remove or change the cakephp pagination count query on postgresql?

问题描述

我正在将cakephp与postgresql一起使用.我的一些表非常大.使用cakephp的分页时，它在大表上运行COUNT()，然后查询实际结果.计数查询需要2.5分钟，而返回数据的实际查询则需要95ms.

I am using cakephp with postgresql. Some of my tables are very large.When using cakephp's pagination, it runs COUNT() on the large table and then it queries for the actual result. The count query takes 2.5 minutes, while the actual query that returns the data takes 95ms.

是否可以消除对计数的需要，或者用速度更快的计数代替它? 我不需要返回确切的行数，就像Google搜索返回结果1-10(约76.5万)的方式一样.

Is it possible to remove the need for the count, or replace it with something that may go faster? I don't need an exact number of rows returned, much like the way a Google search returns results 1-10 of about 765,000.

或者，有没有一种方法可以完全禁用计数?知道不需要多少页通常会有很多结果.我需要的是一个下一步"按钮，该按钮会像cakephp分页一样增加SQL OFFSET，并且如果在下一页上没有返回行，那就很好了.

Alternately, is there a way I can disable count altogether? There will often be so many results knowing how many pages is not necessary. All I need is a "Next" button that increases the SQL OFFSET like the cakephp pagination already has, and if no rows are returned on the next page then that is fine.

推荐答案

我找到了绕过cakephp中分页计数查询的方法. 在要分页的模型中，您可以覆盖运行计数查询的函数，该函数为paginateCount($conditions = null, $recursive = 0, $extra = array())

I found the way to bypass the pagination count query in cakephp. In the model that you want to paginate you can override the function that runs the count query, the function is paginateCount($conditions = null, $recursive = 0, $extra = array())

我使用以下代码解决了我的问题:

I used the following code to fix my issue:

function paginateCount($conditions = null, $recursive = 0, $extra = array()) {
    return 0;
}

然后，我在视图中编辑了分页，以始终显示下一个按钮.转到没有结果的页面只会显示任何内容.

Then I edited the pagination in the view to always display the next button. Going to a page with no results will just display nothing.

这篇关于如何在postgresql上删除或更改cakephp分页计数查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！