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
