Bug #6973
closed
Count query performance low
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.
Updated by krileon over 6 years ago
- 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.
Updated by krileon over 6 years ago
- % 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.
Updated by krileon over 6 years ago
Implement a count_all $output for ->rows encase we do need full table count.
Updated by krileon over 6 years ago
- % Done changed from 30 to 80
Still need some minor optimization in the asset prefetch function.
Updated by krileon over 6 years ago
- 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.