Bug #7497
closed
Added by krileon about 5 years ago.
Updated almost 5 years ago.
Description
When doing an asset prefetch to see if rows exist for an asset it's joining the _user and _comprofiler table unnecessarily and also returning duplicate assets due to missing DISTINCT. The result is a rather slow query. Removing both joins and adding DISTINCT reduces the query from 1s against 500k rows to 1ms providing MASSIVE performance gains.
- Status changed from Assigned to Resolved
- % Done changed from 0 to 100
- Status changed from Resolved to Assigned
- % Done changed from 100 to 80
This is still a bit slow and could take up to 700ms to complete. It no loner takes 2-3s, but is still slow. A dependent subquery with GROUP BY in the parent might bring this down to 200ms or less. Not all the prefetches even need a count; only comments is actually displaying it or needing it so this can be further improved to be faster for non-comment usages.
Another option maybe a separate database table that just keeps track of asset counts. Something like type | asset | count that gets incremented as we don't need a 100% accurate count.
- Status changed from Assigned to Resolved
- % Done changed from 80 to 100
- Target version changed from 936 to 5.0.0
This has been improved a little bit further for single asset prefetch counting. Previously the counting was being done in PHP. With thousands of rows the memory usage would sky rocket. This has been traded for doing the count in SQL resulting in a minor 0-2ms increase in query time, but a 95% decrease in memory usage.
- Status changed from Resolved to Closed
Also available in: Atom
PDF
Updated by