From db27955ad3fae260ee2aa4bace8dd6a4115d338c Mon Sep 17 00:00:00 2001 From: Ludovic Courtès Date: Wed, 14 Feb 2018 18:30:24 +0100 Subject: sql: Add indices to speed up common queries. * src/schema.sql: Add indices. --- src/schema.sql | 6 ++++++ 1 file changed, 6 insertions(+) diff --git a/src/schema.sql b/src/schema.sql index 0ee428c..8d3a651 100644 --- a/src/schema.sql +++ b/src/schema.sql @@ -60,4 +60,10 @@ CREATE TABLE Builds ( FOREIGN KEY (evaluation) REFERENCES Evaluations (id) ); +-- Create indexes to speed up common queries, in particular those +-- corresponding to /api/latestbuilds HTTP requests. +CREATE INDEX Builds_Derivations_index ON Builds(status ASC, derivation, evaluation, stoptime DESC); +CREATE INDEX Specifications_index ON Specifications(repo_name, branch); +CREATE INDEX Derivations_index ON Derivations(derivation, evaluation, job_name, system); + COMMIT; -- cgit v1.2.3