Launchpad itself

Merge lp:~wgrant/launchpad/flatten-bfj-3.75-indices into lp:launchpad

  1. flatten-bfj-3.75-indices
  2. Merge into devel
Proposed by William Grant
Status: Merged
Approved by: William Grant
Approved revision: no longer in the source branch.
Merged at revision: 16477
Proposed branch: lp:~wgrant/launchpad/flatten-bfj-3.75-indices
Merge into: lp:launchpad
Diff against target: 62 lines (+58/-0)
1 file modified
database/schema/patch-2209-41-3.sql (+58/-0)
To merge this branch: bzr merge lp:~wgrant/launchpad/flatten-bfj-3.75-indices
Reviewer Review Type Date Requested Status
William Grant db Approve
Review via email: mp+146562@code.launchpad.net

Commit message

Add various indices for searching on the new BPB/SPRB/BFJ columns.

Description of the change

Add various indices for searching on the new BPB/SPRB/BFJ columns.

To post a comment you must log in.
Revision history for this message
William Grant (wgrant) :
review: Approve (db)

Preview Diff

[H/L] Next/Prev Comment, [J/K] Next/Prev File, [N/P] Next/Prev Hunk
1=== added file 'database/schema/patch-2209-41-3.sql'
2--- database/schema/patch-2209-41-3.sql 1970-01-01 00:00:00 +0000
3+++ database/schema/patch-2209-41-3.sql 2013-02-05 06:16:31 +0000
4@@ -0,0 +1,58 @@
5+-- Copyright 2013 Canonical Ltd. This software is licensed under the
6+-- GNU Affero General Public License version 3 (see the file LICENSE).
7+
8+SET client_min_messages=ERROR;
9+
10+CREATE INDEX buildfarmjob__archive__date_created__id__idx
11+ ON buildfarmjob (archive, date_created DESC, id)
12+ WHERE archive IS NOT NULL;
13+CREATE INDEX buildfarmjob__archive__status__date_created__id__idx
14+ ON buildfarmjob (archive, status, date_created DESC, id)
15+ WHERE archive IS NOT NULL;
16+CREATE INDEX buildfarmjob__archive__status__date_finished__id__idx
17+ ON buildfarmjob (archive, status, date_finished DESC, id)
18+ WHERE archive IS NOT NULL;
19+
20+
21+CREATE INDEX binarypackagebuild__status__id__idx
22+ ON binarypackagebuild (status, id);
23+CREATE INDEX binarypackagebuild__builder__status__date_finished__id__idx
24+ ON binarypackagebuild (builder, status, date_finished DESC, id)
25+ WHERE builder IS NOT NULL;
26+
27+CREATE INDEX binarypackagebuild__archive__status__date_created__id__idx
28+ ON binarypackagebuild (archive, status, date_created DESC, id);
29+CREATE INDEX binarypackagebuild__archive__status__date_finished__id__idx
30+ ON binarypackagebuild (archive, status, date_finished DESC, id);
31+
32+CREATE INDEX binarypackagebuild__das__status__date_finished__id__idx
33+ ON binarypackagebuild (distro_arch_series, status, date_finished DESC, id);
34+CREATE INDEX binarypackagebuild__das__status__id__idx
35+ ON binarypackagebuild (distro_arch_series, status, id);
36+CREATE INDEX binarypackagebuild__das__id__idx
37+ ON binarypackagebuild (distro_arch_series, id);
38+
39+CREATE INDEX binarypackagebuild__spr__archive__status__idx
40+ ON binarypackagebuild (source_package_release, archive, status);
41+CREATE INDEX binarypackagebuild__spr__distro_arch_series__status__idx
42+ ON binarypackagebuild (source_package_release, distro_arch_series, status);
43+
44+
45+CREATE INDEX sourcepackagerecipebuild__recipe__date_created__idx
46+ ON sourcepackagerecipebuild (recipe, date_created DESC);
47+CREATE INDEX sourcepackagerecipebuild__recipe__started__finished__created__idx
48+ ON sourcepackagerecipebuild (
49+ recipe, GREATEST(date_started, date_finished) DESC, date_created DESC,
50+ id DESC);
51+CREATE INDEX sourcepackagerecipebuild__recipe__started__finished__idx
52+ ON sourcepackagerecipebuild (
53+ recipe, GREATEST(date_started, date_finished) DESC, id DESC);
54+CREATE INDEX sourcepackagerecipebuild__recipe__status__id__idx
55+ ON sourcepackagerecipebuild (recipe, status, id DESC);
56+CREATE INDEX sourcepackagerecipebuild__recipe__date_finished__idx
57+ ON sourcepackagerecipebuild (recipe, date_finished DESC);
58+
59+CREATE INDEX binarypackagepublishinghistory__archive__bpr__status__idx
60+ ON binarypackagepublishinghistory (archive, binarypackagerelease, status);
61+
62+INSERT INTO LaunchpadDatabaseRevision VALUES (2209, 41, 3);