Feature proposal #7503
closed
Improve paging behavior
Description
Currently paging is doing a count query before the select query, which isn't really necessary. We simply need to do the select query and add +1 to the LIMIT. So for example we want 15 entries, grab 16. If there are 16 we know there's another page. This completely eliminates an unnecessary query.
Updated by krileon about 5 years ago
Consider reviewing new pagination options as we don't need to know the total for anything other than Like, Comment, and Tags which is handled by prefetch.
Updated by krileon about 5 years ago
This is the only thing holding back remaining performance improvements. Currently the following test environment is giving the below results.
Rows: 1 million
Count: 20ms
Result: 1ms
In short the count query, just to see if there's another page not to actually display count, is 20 TIMES SLOWER. There's just no need for this. In the SELECT just grab more than 1 row than what we need and set in the stream that there's another page.
Note second query is 1ms because it's acting off SQL memory. The overall performance gain will likely be around 2-5ms and save a little memory.
Updated by krileon about 5 years ago
- % Done changed from 0 to 50
Average queries are now 0.5-10ms instead of 20-30ms. Substantial improvements across all the streams paging behaviors. These are all still using LIMIT offset, but there may not be a way of eliminating that. All of the old ->rows( 'count' ) usages probably need to be removed at this point, which would give PAGE_LIMIT + 1 count results, but need to review if they may still be needed.
Updated by krileon about 5 years ago
- % Done changed from 50 to 80
2 ->rows( 'count' ) usages left to evaluate followed by removal of 'count_all', which will just become 'count'.
Updated by krileon about 5 years ago
- Status changed from Assigned to Resolved
- % Done changed from 80 to 100
Paging no longer does or needs a count. ->rows( 'count_all' ) is also just ->rows( 'count' ) now (removing count_all).