Bug #6973
closed
Count query performance low
Added by krileon almost 7 years ago.
Updated almost 7 years ago.
Start date:
09 January 2018
Description
The count queries, while indexed, are still slow when hitting 100k+ rows (roughly 100-500ms). These can be eliminated entirely in most cases once timestamp pagination is implemented. However, we may still need counts for some usages (e.g. comment counts). To improve this further we maybe able to do a SUM count for a rough estimate, which will be fine for those usages or use a non-filtered count output.
- Target version changed from 6.0.0 to 4.2.0
- Target version changed from 4.2.0 to 6.0.0
There's no improving this without changing to timestamp based paging. InnoDB doesn't optimize for count so it always has to do full table stacks. Even with simplified count query it still takes a reasonable amount of time to count against 100k+ table.
- Target version changed from 6.0.0 to 4.2.0
- % Done changed from 0 to 30
Seams to be a relatively easy fix by doing a "soft" count. Basically we grab the number of entries needed per page + 1 and we only grab their IDs. We then count the IDs and if grater than the page limit we know there's more than 1 page so show the MORE button. We also know if there's entries or not without having to do a full table scan for total count, which in most cases we don't even need.
Implement a count_all $output for ->rows encase we do need full table count.
- % Done changed from 30 to 80
Still need some minor optimization in the asset prefetch function.
- Status changed from Assigned to Resolved
- % Done changed from 80 to 100
Asset prefetch array already included asset counts. It now can pass those on so the count query doesn't have to happen again when already prefetched. This effectively eliminates a lot of extra queries.
- Status changed from Resolved to Closed
Also available in: Atom
PDF
Updated by