From 64504b636470ad1048ba6310d6bd2dff8a28b914 Mon Sep 17 00:00:00 2001 From: Zeger-Jan van de Weg Date: Tue, 13 Oct 2015 16:04:36 +0200 Subject: Add an index to milestones title and label title --- db/migrate/20151013133938_add_index_to_milestones.rb | 6 ++++++ db/schema.rb | 2 ++ 2 files changed, 8 insertions(+) create mode 100644 db/migrate/20151013133938_add_index_to_milestones.rb diff --git a/db/migrate/20151013133938_add_index_to_milestones.rb b/db/migrate/20151013133938_add_index_to_milestones.rb new file mode 100644 index 00000000000..41cd91e570b --- /dev/null +++ b/db/migrate/20151013133938_add_index_to_milestones.rb @@ -0,0 +1,6 @@ +class AddIndexToMilestones < ActiveRecord::Migration + def change + add_index :milestones, :title + add_index :labels, :title + end +end diff --git a/db/schema.rb b/db/schema.rb index 7a11dfca034..68bd9d2c3e5 100644 --- a/db/schema.rb +++ b/db/schema.rb @@ -410,6 +410,7 @@ ActiveRecord::Schema.define(version: 20151008130321) do end add_index "labels", ["project_id"], name: "index_labels_on_project_id", using: :btree + add_index "labels", ["title"], name: "index_labels_on_title", using: :btree create_table "members", force: true do |t| t.integer "access_level", null: false @@ -491,6 +492,7 @@ ActiveRecord::Schema.define(version: 20151008130321) do add_index "milestones", ["due_date"], name: "index_milestones_on_due_date", using: :btree add_index "milestones", ["project_id", "iid"], name: "index_milestones_on_project_id_and_iid", unique: true, using: :btree add_index "milestones", ["project_id"], name: "index_milestones_on_project_id", using: :btree + add_index "milestones", ["title"], name: "index_milestones_on_title", using: :btree create_table "namespaces", force: true do |t| t.string "name", null: false -- cgit v1.2.1 From b5762104abbf373d69a20532de08564eb9ae93f6 Mon Sep 17 00:00:00 2001 From: Zeger-Jan van de Weg Date: Tue, 13 Oct 2015 16:05:04 +0200 Subject: Minor refactoring in seeding --- db/fixtures/development/05_users.rb | 4 ++-- db/fixtures/development/07_milestones.rb | 2 +- db/fixtures/development/09_issues.rb | 2 +- db/fixtures/development/12_snippets.rb | 2 +- 4 files changed, 5 insertions(+), 5 deletions(-) diff --git a/db/fixtures/development/05_users.rb b/db/fixtures/development/05_users.rb index 378354efd5a..03da29c4c68 100644 --- a/db/fixtures/development/05_users.rb +++ b/db/fixtures/development/05_users.rb @@ -1,5 +1,5 @@ Gitlab::Seeder.quiet do - (2..20).each do |i| + 20.times do |i| begin User.create!( username: FFaker::Internet.user_name, @@ -15,7 +15,7 @@ Gitlab::Seeder.quiet do end end - (1..5).each do |i| + 5.times do |i| begin User.create!( username: "user#{i}", diff --git a/db/fixtures/development/07_milestones.rb b/db/fixtures/development/07_milestones.rb index a43116829d9..e028ac82ba3 100644 --- a/db/fixtures/development/07_milestones.rb +++ b/db/fixtures/development/07_milestones.rb @@ -1,6 +1,6 @@ Gitlab::Seeder.quiet do Project.all.each do |project| - (1..5).each do |i| + 5.times do |i| milestone_params = { title: "v#{i}.0", description: FFaker::Lorem.sentence, diff --git a/db/fixtures/development/09_issues.rb b/db/fixtures/development/09_issues.rb index c636e96381c..4fa572fca9b 100644 --- a/db/fixtures/development/09_issues.rb +++ b/db/fixtures/development/09_issues.rb @@ -1,6 +1,6 @@ Gitlab::Seeder.quiet do Project.all.each do |project| - (1..10).each do |i| + 10.times do issue_params = { title: FFaker::Lorem.sentence(6), description: FFaker::Lorem.sentence, diff --git a/db/fixtures/development/12_snippets.rb b/db/fixtures/development/12_snippets.rb index 3bd4b442ade..74898544a69 100644 --- a/db/fixtures/development/12_snippets.rb +++ b/db/fixtures/development/12_snippets.rb @@ -22,7 +22,7 @@ class Member < ActiveRecord::Base end eos - (1..50).each do |i| + 50.times do |i| user = User.all.sample PersonalSnippet.seed(:id, [{ -- cgit v1.2.1 From ac44e3844deac1c13f35ca0e9a7ce995be58aab7 Mon Sep 17 00:00:00 2001 From: Zeger-Jan van de Weg Date: Wed, 14 Oct 2015 12:20:48 +0200 Subject: Add project scope to milestone search --- app/finders/issuable_finder.rb | 9 ++++++++- 1 file changed, 8 insertions(+), 1 deletion(-) diff --git a/app/finders/issuable_finder.rb b/app/finders/issuable_finder.rb index 97c7e74c294..d60f36e1aff 100644 --- a/app/finders/issuable_finder.rb +++ b/app/finders/issuable_finder.rb @@ -81,7 +81,14 @@ class IssuableFinder @milestones = if milestones? - Milestone.where(title: params[:milestone_title]) + scope = + if project + project.milestones + else + Milestone.all + end + + scope.where(title: params[:milestone_title]) else nil end -- cgit v1.2.1 From 9127ae5ca80aa06b0a83d275e2a2d9b7ccfbfc3d Mon Sep 17 00:00:00 2001 From: Zeger-Jan van de Weg Date: Wed, 14 Oct 2015 12:23:49 +0200 Subject: Improve performance of queries Credits to Douwe Maan --- CHANGELOG | 1 + app/finders/issuable_finder.rb | 74 ++++++++++++++-------- .../20151013133938_add_index_to_milestones.rb | 6 -- 3 files changed, 47 insertions(+), 34 deletions(-) delete mode 100644 db/migrate/20151013133938_add_index_to_milestones.rb diff --git a/CHANGELOG b/CHANGELOG index 814a6772cfd..8f696009d61 100644 --- a/CHANGELOG +++ b/CHANGELOG @@ -63,6 +63,7 @@ v 8.1.0 (unreleased) - Only render 404 page from /public - Hide passwords from services API (Alex Lossent) - Fix: Images cannot show when projects' path was changed + - Optimize query when filtering on issuables (Zeger-Jan van de Weg) v 8.0.4 - Fix Message-ID header to be RFC 2111-compliant to prevent e-mails being dropped (Stan Hu) diff --git a/app/finders/issuable_finder.rb b/app/finders/issuable_finder.rb index d60f36e1aff..3170c0f672e 100644 --- a/app/finders/issuable_finder.rb +++ b/app/finders/issuable_finder.rb @@ -53,15 +53,36 @@ class IssuableFinder end end + def project? + params[:project_id].present? + end + def project return @project if defined?(@project) - @project = - if params[:project_id].present? - Project.find(params[:project_id]) - else - nil - end + if project? + @project = Project.find(params[:project_id]) + + unless Ability.abilities.allowed?(current_user, :read_project, @project) + @project = nil + end + else + @project = nil + end + + @project + end + + def projects + return if project? + + return @projects if defined?(@projects) + + if current_user && params[:authorized_only].presence && !current_user_related? + current_user.authorized_projects + else + ProjectsFinder.new.execute(current_user) + end end def search @@ -84,8 +105,10 @@ class IssuableFinder scope = if project project.milestones + elsif projects + Milestone.where(project_id: projects) else - Milestone.all + Milestone.none end scope.where(title: params[:milestone_title]) @@ -127,19 +150,7 @@ class IssuableFinder private def init_collection - table_name = klass.table_name - - if project - if Ability.abilities.allowed?(current_user, :read_project, project) - project.send(table_name) - else - [] - end - elsif current_user && params[:authorized_only].presence && !current_user_related? - klass.of_projects(current_user.authorized_projects).references(:project) - else - klass.of_projects(ProjectsFinder.new.execute(current_user)).references(:project) - end + klass.all end def by_scope(items) @@ -177,7 +188,14 @@ class IssuableFinder end def by_project(items) - items = items.of_projects(project.id) if project + items = + if project + items.of_projects(project) + elsif projects + items.of_projects(projects).references(:project) + else + items.none + end items end @@ -223,17 +241,17 @@ class IssuableFinder def by_label(items) if params[:label_name].present? if params[:label_name] == Label::None.title - item_ids = LabelLink.where(target_type: klass.name).pluck(:target_id) - - items = items.where('id NOT IN (?)', item_ids) + items = items. + joins("LEFT OUTER JOIN label_links ON label_links.target_type = '#{klass.name}' AND label_links.target_id = #{klass.table_name}.id"). + where(label_links: { id: nil }) else label_names = params[:label_name].split(",") - item_ids = LabelLink.joins(:label). - where('labels.title in (?)', label_names). - where(target_type: klass.name).pluck(:target_id) + items = items.joins(:labels).where(labels: { title: label_names }) - items = items.where(id: item_ids) + if project + items = items.where('labels.project_id = :id', id: project.id) + end end end diff --git a/db/migrate/20151013133938_add_index_to_milestones.rb b/db/migrate/20151013133938_add_index_to_milestones.rb deleted file mode 100644 index 41cd91e570b..00000000000 --- a/db/migrate/20151013133938_add_index_to_milestones.rb +++ /dev/null @@ -1,6 +0,0 @@ -class AddIndexToMilestones < ActiveRecord::Migration - def change - add_index :milestones, :title - add_index :labels, :title - end -end -- cgit v1.2.1 From 0108cdf49514dcaccc6a53c7b6e257fa9acfea98 Mon Sep 17 00:00:00 2001 From: Douwe Maan Date: Fri, 16 Oct 2015 11:43:26 +0200 Subject: Improve performance of filtering issues by milestone --- app/finders/issuable_finder.rb | 63 ++++++++++++++++++++++-------------------- 1 file changed, 33 insertions(+), 30 deletions(-) diff --git a/app/finders/issuable_finder.rb b/app/finders/issuable_finder.rb index 3170c0f672e..f00bb02d0fb 100644 --- a/app/finders/issuable_finder.rb +++ b/app/finders/issuable_finder.rb @@ -74,11 +74,11 @@ class IssuableFinder end def projects - return if project? - return @projects if defined?(@projects) - if current_user && params[:authorized_only].presence && !current_user_related? + if project? + project + elsif current_user && params[:authorized_only].presence && !current_user_related? current_user.authorized_projects else ProjectsFinder.new.execute(current_user) @@ -102,14 +102,7 @@ class IssuableFinder @milestones = if milestones? - scope = - if project - project.milestones - elsif projects - Milestone.where(project_id: projects) - else - Milestone.none - end + scope = Milestone.where(project_id: projects) scope.where(title: params[:milestone_title]) else @@ -117,6 +110,14 @@ class IssuableFinder end end + def labels? + params[:label_name].present? + end + + def no_labels? + labels? && params[:label_name] == Label::None.title + end + def assignee? params[:assignee_id].present? end @@ -189,9 +190,7 @@ class IssuableFinder def by_project(items) items = - if project - items.of_projects(project) - elsif projects + if projects items.of_projects(projects).references(:project) else items.none @@ -210,18 +209,6 @@ class IssuableFinder items.sort(params[:sort]) end - def by_milestone(items) - if milestones? - if no_milestones? - items = items.where(milestone_id: [-1, nil]) - else - items = items.where(milestone_id: milestones.try(:pluck, :id)) - end - end - - items - end - def by_assignee(items) if assignee? items = items.where(assignee_id: assignee.try(:id)) @@ -238,9 +225,25 @@ class IssuableFinder items end + def by_milestone(items) + if milestones? + if no_milestones? + items = items.where(milestone_id: [-1, nil]) + else + items = items.joins(:milestone).where(milestones: { title: params[:milestone_title] }) + + if projects + items = items.where(milestones: { project_id: projects }) + end + end + end + + items + end + def by_label(items) - if params[:label_name].present? - if params[:label_name] == Label::None.title + if labels? + if no_labels? items = items. joins("LEFT OUTER JOIN label_links ON label_links.target_type = '#{klass.name}' AND label_links.target_id = #{klass.table_name}.id"). where(label_links: { id: nil }) @@ -249,8 +252,8 @@ class IssuableFinder items = items.joins(:labels).where(labels: { title: label_names }) - if project - items = items.where('labels.project_id = :id', id: project.id) + if projects + items = items.where(labels: { project_id: projects }) end end end -- cgit v1.2.1 From aafb36616cbad9b3478964dffc10fca97c2f55bb Mon Sep 17 00:00:00 2001 From: Douwe Maan Date: Sun, 18 Oct 2015 14:26:25 +0200 Subject: Fix schema [ci skip] --- db/schema.rb | 2 -- 1 file changed, 2 deletions(-) diff --git a/db/schema.rb b/db/schema.rb index 7d60e3cf9e3..886b05f3e56 100644 --- a/db/schema.rb +++ b/db/schema.rb @@ -416,7 +416,6 @@ ActiveRecord::Schema.define(version: 20151016195706) do end add_index "labels", ["project_id"], name: "index_labels_on_project_id", using: :btree - add_index "labels", ["title"], name: "index_labels_on_title", using: :btree create_table "members", force: true do |t| t.integer "access_level", null: false @@ -498,7 +497,6 @@ ActiveRecord::Schema.define(version: 20151016195706) do add_index "milestones", ["due_date"], name: "index_milestones_on_due_date", using: :btree add_index "milestones", ["project_id", "iid"], name: "index_milestones_on_project_id_and_iid", unique: true, using: :btree add_index "milestones", ["project_id"], name: "index_milestones_on_project_id", using: :btree - add_index "milestones", ["title"], name: "index_milestones_on_title", using: :btree create_table "namespaces", force: true do |t| t.string "name", null: false -- cgit v1.2.1 From 8b8fbd4e7f51db32425dd30770c1efcea75c00f7 Mon Sep 17 00:00:00 2001 From: Douwe Maan Date: Mon, 19 Oct 2015 11:46:22 +0200 Subject: Rename confusing methods --- app/finders/issuable_finder.rb | 8 ++++---- 1 file changed, 4 insertions(+), 4 deletions(-) diff --git a/app/finders/issuable_finder.rb b/app/finders/issuable_finder.rb index f00bb02d0fb..c407dfc163a 100644 --- a/app/finders/issuable_finder.rb +++ b/app/finders/issuable_finder.rb @@ -93,7 +93,7 @@ class IssuableFinder params[:milestone_title].present? end - def no_milestones? + def filter_by_no_milestone? milestones? && params[:milestone_title] == Milestone::None.title end @@ -114,7 +114,7 @@ class IssuableFinder params[:label_name].present? end - def no_labels? + def filter_by_no_label? labels? && params[:label_name] == Label::None.title end @@ -227,7 +227,7 @@ class IssuableFinder def by_milestone(items) if milestones? - if no_milestones? + if filter_by_no_milestone? items = items.where(milestone_id: [-1, nil]) else items = items.joins(:milestone).where(milestones: { title: params[:milestone_title] }) @@ -243,7 +243,7 @@ class IssuableFinder def by_label(items) if labels? - if no_labels? + if filter_by_no_label? items = items. joins("LEFT OUTER JOIN label_links ON label_links.target_type = '#{klass.name}' AND label_links.target_id = #{klass.table_name}.id"). where(label_links: { id: nil }) -- cgit v1.2.1