Patch #37057
closed
Query optimization for attachments activity
Description
The LEFT JOIN with an OR in its clause in the first acts_as_activity_provider call in attachment.rb can cause very long query times for large numbers of attachments (on MySQL, at least).
One example we observed was on Users#show, where the query for activity on version / project attachments took 30 seconds (and yielded zero results) for a user that authored over 100k attachments (but none of them were attached to versions or projects).
This patch that was extracted from Planio adds a `where` clause that limits the number of attachments that are at all considered for the joins to those that are actually attached to a version or project.
Files
Updated by Pavel Rosický over 2 years ago
- File attachment2.patch attachment2.patch added
nice optimization, I think we could use the same trick on Documents
Updated by Go MAEDA over 2 years ago
- File 37057.patch 37057.patch added
- Target version set to 5.1.0
Setting the target version to 5.1.0.
Updated by Holger Just over 2 years ago
Pavel Rosický wrote:
nice optimization, I think we could use the same trick on Documents
Technically yes. However, as the joins used there are a straight line there (attachments -> documents -> projects), MySQL's query optimizer already chooses a fast query plan in this case. As such, we have not observed any significant speedup there when using the additional restriction.
With that being said, it likely won't hurt either :)
Updated by Go MAEDA over 2 years ago
- Subject changed from query optimization for attachments activity to Query optimization for attachments activity
- Status changed from New to Closed
- Assignee set to Go MAEDA
Committed the patch. Thank you.