Fix slow loading of global spent time list in MySQL (#40728).

In MySQL, the query to retrieve the global spent time list is sometimes extremely slow (taking several minutes in some environments) due to an inefficient join order chosen by the query optimizer. This patch adds an optimizer hint to improve the join order and ensure consistent performance.

Patch by Go MAEDA (user:maeda).


git-svn-id: https://svn.redmine.org/redmine/trunk@23609 e93f8b46-1217-0410-a6f0-8f06a7374b81

1 files changed, 13 insertions, 6 deletions

diff --git a/app/models/time_entry_query.rb b/app/models/time_entry_query.rb
index 41997180b..82b895671 100644
--- a/app/models/time_entry_query.rb
+++ b/app/models/time_entry_query.rb
@@ -164,12 +164,19 @@ class TimeEntryQuery < Query
   end
 
   def base_scope
-    TimeEntry.visible.
-      joins(:project, :user).
-      includes(:activity).
-      references(:activity).
-      left_join_issue.
-      where(statement)
+    scope = TimeEntry.visible
+                     .joins(:project, :user)
+                     .includes(:activity)
+                     .references(:activity)
+                     .left_join_issue
+                     .where(statement)
+
+    if Redmine::Database.mysql? && ActiveRecord::Base.connection.supports_optimizer_hints?
+      # Provides MySQL with a hint to use a better join order and avoid slow response times
+      scope.optimizer_hints('JOIN_ORDER(time_entries, projects, users)')
+    else
+      scope
+    end
   end
 
   def results_scope(options={})