From: Christopher Baines <mail@cbaines.net>
To: guix-devel@gnu.org
Subject: [PATCH] sql: Add a couple of indexes.
Date: Tue, 9 Jun 2020 21:53:42 +0100 [thread overview]
Message-ID: <20200609205342.613-1-mail@cbaines.net> (raw)
Remove the Builds_index from the schema, as it would have been removed in
error by the upgrade-6 upgrade. Add a specific index on the Builds status
field, as this helps with db-get-builds queries, and add an index on the
Outputs derivation field, as this helps with the db-get-outputs part of
db-get-builds.
* src/sql/upgrade-7.sql: New file.
* src/schema.sql: Update accordingly.
---
src/schema.sql | 3 ++-
src/sql/upgrade-7.sql | 7 +++++++
2 files changed, 9 insertions(+), 1 deletion(-)
create mode 100644 src/sql/upgrade-7.sql
diff --git a/src/schema.sql b/src/schema.sql
index 1104551..60d250a 100644
--- a/src/schema.sql
+++ b/src/schema.sql
@@ -74,7 +74,8 @@ CREATE TABLE Events (
-- Create indexes to speed up common queries, in particular those
-- corresponding to /api/latestbuilds and /api/queue HTTP requests.
-CREATE INDEX Builds_index ON Builds(job_name, system, status ASC, timestamp ASC, derivation, evaluation, stoptime DESC);
+CREATE INDEX Builds_status_index ON Builds (status);
+CREATE INDEX Outputs_derivation_index ON Outputs (derivation);
CREATE INDEX Inputs_index ON Inputs(specification, name, branch);
COMMIT;
diff --git a/src/sql/upgrade-7.sql b/src/sql/upgrade-7.sql
new file mode 100644
index 0000000..1be3470
--- /dev/null
+++ b/src/sql/upgrade-7.sql
@@ -0,0 +1,7 @@
+BEGIN TRANSACTION;
+
+CREATE INDEX Builds_status_index ON Builds (status);
+
+CREATE INDEX Outputs_derivation_index ON Outputs (derivation);
+
+COMMIT;
--
2.26.2
next reply other threads:[~2020-06-09 21:03 UTC|newest]
Thread overview: 6+ messages / expand[flat|nested] mbox.gz Atom feed top
2020-06-09 20:53 Christopher Baines [this message]
2020-06-13 16:29 ` [PATCH] sql: Add a couple of indexes Christopher Baines
2020-06-13 18:09 ` zimoun
2020-06-14 11:48 ` Christopher Baines
2020-06-15 9:45 ` zimoun
2020-06-15 18:12 ` Christopher Baines
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
Avoid top-posting and favor interleaved quoting:
https://en.wikipedia.org/wiki/Posting_style#Interleaved_style
List information: https://guix.gnu.org/
* Reply using the --to, --cc, and --in-reply-to
switches of git-send-email(1):
git send-email \
--in-reply-to=20200609205342.613-1-mail@cbaines.net \
--to=mail@cbaines.net \
--cc=guix-devel@gnu.org \
/path/to/YOUR_REPLY
https://kernel.org/pub/software/scm/git/docs/git-send-email.html
* If your mail client supports setting the In-Reply-To header
via mailto: links, try the mailto: link
Be sure your reply has a Subject: header at the top and a blank line
before the message body.
Code repositories for project(s) associated with this public inbox
https://git.savannah.gnu.org/cgit/guix.git
This is a public inbox, see mirroring instructions
for how to clone and mirror all data and code used for this inbox;
as well as URLs for read-only IMAP folder(s) and NNTP newsgroup(s).