Patch #37057

Query optimization for attachments activity

Added by Jens Krämer 5 months ago. Updated 5 months ago.

Status:ClosedStart date:
Priority:NormalDue date:
Assignee:Go MAEDA% Done:

0%

Category:Performance
Target version:5.1.0

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.

0001-Attachment-activity-provider-sql-optimization.patch Magnifier (1.06 KB) Jens Krämer, 2022-04-28 13:34

attachment2.patch Magnifier (493 Bytes) Pavel Rosický, 2022-04-28 18:21

37057.patch Magnifier (813 Bytes) Go MAEDA, 2022-04-30 09:48

Associated revisions

Revision 21573
Added by Go MAEDA 5 months ago

Attachment activity provider SQL optimization (#37057).

Patch by Jens Krämer.

History

#1 Updated by Pavel Rosický 5 months ago

nice optimization, I think we could use the same trick on Documents

#2 Updated by Go MAEDA 5 months ago

Setting the target version to 5.1.0.

#3 Updated by Holger Just 5 months 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 :)

#4 Updated by Go MAEDA 5 months 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.

Also available in: Atom PDF