Project

General

Profile

Actions

Bug #6973

closed

Count query performance low

Added by krileon over 6 years ago. Updated about 6 years ago.

Status:
Closed
Priority:
High
Assignee:
Target version:
Start date:
09 January 2018
Due date:
% Done:

100%

Estimated time:

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.

Actions #1

Updated by krileon over 6 years ago

  • Target version changed from 6.0.0 to 4.2.0
Actions #2

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.

Actions #3

Updated by krileon over 6 years ago

  • Target version changed from 6.0.0 to 4.2.0
Actions #4

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.

Actions #5

Updated by krileon over 6 years ago

Implement a count_all $output for ->rows encase we do need full table count.

Actions #6

Updated by krileon over 6 years ago

  • % Done changed from 30 to 80

Still need some minor optimization in the asset prefetch function.

Actions #7

Updated by krileon about 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.

Actions #8

Updated by krileon about 6 years ago

  • Status changed from Resolved to Closed
Actions

Also available in: Atom PDF