From 7f71d9b7ce33ffbe7102061a11ebb78d47c315b4 Mon Sep 17 00:00:00 2001 From: Christopher Baines Date: Tue, 9 Jun 2020 21:46:49 +0100 Subject: sql: Add a couple of indexes. 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-8.sql: New file. * src/schema.sql: Update accordingly. --- src/schema.sql | 3 ++- src/sql/upgrade-8.sql | 7 +++++++ 2 files changed, 9 insertions(+), 1 deletion(-) create mode 100644 src/sql/upgrade-8.sql diff --git a/src/schema.sql b/src/schema.sql index 8ae0a85..5ea1ff7 100644 --- a/src/schema.sql +++ b/src/schema.sql @@ -85,7 +85,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-8.sql b/src/sql/upgrade-8.sql new file mode 100644 index 0000000..1be3470 --- /dev/null +++ b/src/sql/upgrade-8.sql @@ -0,0 +1,7 @@ +BEGIN TRANSACTION; + +CREATE INDEX Builds_status_index ON Builds (status); + +CREATE INDEX Outputs_derivation_index ON Outputs (derivation); + +COMMIT; -- cgit v1.2.3