Bug #7497
closed
Prefetch query too slow
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.
Updated by krileon about 5 years ago
- Status changed from Assigned to Resolved
- % Done changed from 0 to 100
Updated by krileon about 5 years ago
- 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.
Updated by krileon about 5 years ago
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.
Updated by krileon about 5 years ago
- Status changed from Assigned to Resolved
- % Done changed from 80 to 100
Updated by krileon about 5 years ago
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.