Patch #38198: Improve MySQL query plan for Project#project_condition - Redmine

Actions

Copy link

Patch #38198

closed

Improve MySQL query plan for Project#project_condition

Added by Holger Just almost 2 years ago. Updated almost 2 years ago.

Status:

Closed

Priority:

Normal

Assignee:

Go MAEDA

Category:

Performance

Target version:

5.1.0

Start date:

Due date:

% Done:

Estimated time:

Description

On a large installations (>30k projects, >500k issues), rendering the issue statistics on a project overview page can take a long time. We observed runtimes for the SQL queries of > 5 seconds for the queries generated in ProjectsController#show

@open_issues_by_tracker = Issue.visible.open.where(cond).group(:tracker).count
@total_issues_by_tracker = Issue.visible.where(cond).group(:tracker).count

@total_hours = TimeEntry.visible.where(cond).sum(:hours).to_f
@total_estimated_hours = Issue.visible.where(cond).sum(:estimated_hours).to_f

As an example, the query plan for one query for @total_estimated_hours was as follows:

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	issues	ALL	issues_project_id	NULL	NULL	NULL	448033	Using where
1	PRIMARY	projects	eq_ref	PRIMARY,index_projects_on_lft,index_projects_on_rgt	PRIMARY	4	redmine.issues.project_id	1	Using where
3	SUBQUERY	members	range	index_members_on_user_id_and_project_id,index_members_on_user_id,index_members_on_project_id	index_members_on_user_id_and_project_id	4	NULL	8	Using where; Using index
2	DEPENDENT SUBQUERY	em	ref	enabled_modules_project_id	enabled_modules_project_id	5	redmine.projects.id	4	Using where

This query took more than 5 seconds in MySQL. The query used both the projects.id as well as the projects.lft / project.rgt columns. This caused MySQL to perform a table scan on the (large) time_entries or issues tables followed by an index-scan on the projects table.

With the change in the attached patch change, MySQL first filters the projects followed by the issues/ time entries. This allows MySQL to use the project_id index on the issues table after performing a table scan on the (smaller) projects table. The query plan for this improved query is:

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	projects	ALL	PRIMARY,index_projects_on_lft,index_projects_on_rgt	NULL	NULL	NULL	39606	Using where
1	PRIMARY	issues	ref	issues_project_id	issues_project_id	4	hostedredmine.projects.id	14	Using where
3	SUBQUERY	members	range	index_members_on_user_id_and_project_id,index_members_on_user_id,index_members_on_project_id	index_members_on_user_id_and_project_id	4	NULL	8	Using where; Using index
2	DEPENDENT SUBQUERY	em	ref	enabled_modules_project_id	enabled_modules_project_id	5	hostedredmine.projects.id	4	Using where