search

gobuffalo / pop

A Tasty Treat For All Your Database Needs
MIT License
1.43k stars 241 forks source link

Allow COUNT(*) query to be disabled for pagination queries #394

Open deelawn opened 5 years ago

deelawn commented 5 years ago

Currently, a COUNT(*) query is executed after any query that includes pagination. This can be a problem when the table being queried is very large. Due to how the query is executed; a derived table is constructed from source being queried against, which can cause serious query performance issues depending on the size of the table.

Reproduce this issue by running a paginated query against a very large table.

There should be an option somewhere that disables running COUNT(*) for paginated queries. I'm looking for some guidance as to where would be the best place to make this change. My initial thought is to add an unexported field named deferStatsCollection to the Paginator struct. An exported method would be defined to enable setting this flag. When true, no query stats would be collected that are used to populate the Paginator struct after the query (no COUNT(*) query being run).

pop v4.10.0

deelawn commented 5 years ago

On second look, I think performance could be improved by rewriting the query to simply select COUNT(*) from the same data source rather than select from a derived table; replace everything between SELECT and FROM with COUNT(*).

byungjikroh commented 4 years ago

A vote for this feature.

It's necessary to skip 'SELECT COUNT' query for performance on large data. The other programming languages pagination libraries support this.

Please consider to add.

sio4 commented 1 year ago

Also #631 (closed as duplicated)

Right now go-buffalo does not support counting with a limit, this causes that in paginated queries for tables with millions of items, no matter how small amount of items we query per page, the time to execute the query will be directly proportional to the amount of items within the table, and in some extremes, we can see paginated queries take just milliseconds to query the items while taking minutes to query the count.

I propose to have a way to either configure the Paginator model to include a property such as MaxPageCount that is used to limit the amount of pages we count while paginating, as most user facing cases, users will never paginate beyond i.e., 99 pages.