From mboxrd@z Thu Jan 1 00:00:00 1970 Return-Path: Received: from mp1 ([2001:41d0:2:4a6f::]) (using TLSv1.3 with cipher TLS_AES_256_GCM_SHA384 (256/256 bits)) by ms11 with LMTPS id yL28Az/5315mVwAA0tVLHw (envelope-from ) for ; Tue, 09 Jun 2020 21:03:59 +0000 Received: from aspmx1.migadu.com ([2001:41d0:2:4a6f::]) (using TLSv1.3 with cipher TLS_AES_256_GCM_SHA384 (256/256 bits)) by mp1 with LMTPS id Af8JOz75316gDQAAbx9fmQ (envelope-from ) for ; Tue, 09 Jun 2020 21:03:58 +0000 Received: from lists.gnu.org (lists.gnu.org [209.51.188.17]) (using TLSv1.2 with cipher ECDHE-RSA-AES256-GCM-SHA384 (256/256 bits)) (No client certificate requested) by aspmx1.migadu.com (Postfix) with ESMTPS id EC6F7940669 for ; Tue, 9 Jun 2020 21:03:57 +0000 (UTC) Received: from localhost ([::1]:41190 helo=lists1p.gnu.org) by lists.gnu.org with esmtp (Exim 4.90_1) (envelope-from ) id 1jilPY-0000Lg-Lv for larch@yhetil.org; Tue, 09 Jun 2020 17:03:56 -0400 Received: from eggs.gnu.org ([2001:470:142:3::10]:36502) by lists.gnu.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.90_1) (envelope-from ) id 1jilFk-0003Zm-Cd for guix-devel@gnu.org; Tue, 09 Jun 2020 16:53:49 -0400 Received: from mira.cbaines.net ([212.71.252.8]:60772) by eggs.gnu.org with esmtp (Exim 4.90_1) (envelope-from ) id 1jilFj-0005hz-0d for guix-devel@gnu.org; Tue, 09 Jun 2020 16:53:47 -0400 Received: from localhost (unknown [46.237.175.96]) by mira.cbaines.net (Postfix) with ESMTPSA id BE48D27BBE1 for ; Tue, 9 Jun 2020 21:53:44 +0100 (BST) Received: from localhost (localhost [local]) by localhost (OpenSMTPD) with ESMTPA id 6d850065 for ; Tue, 9 Jun 2020 20:53:42 +0000 (UTC) From: Christopher Baines To: guix-devel@gnu.org Subject: [PATCH] sql: Add a couple of indexes. Date: Tue, 9 Jun 2020 21:53:42 +0100 Message-Id: <20200609205342.613-1-mail@cbaines.net> X-Mailer: git-send-email 2.26.2 MIME-Version: 1.0 Content-Transfer-Encoding: 8bit Received-SPF: pass client-ip=212.71.252.8; envelope-from=mail@cbaines.net; helo=mira.cbaines.net X-detected-operating-system: by eggs.gnu.org: First seen = 2020/06/09 16:53:45 X-ACL-Warn: Detected OS = Linux 2.2.x-3.x [generic] X-Spam_score_int: -18 X-Spam_score: -1.9 X-Spam_bar: - X-Spam_report: (-1.9 / 5.0 requ) BAYES_00=-1.9, SPF_HELO_PASS=-0.001, SPF_PASS=-0.001, UNPARSEABLE_RELAY=0.001 autolearn=_AUTOLEARN X-Spam_action: no action X-BeenThere: guix-devel@gnu.org X-Mailman-Version: 2.1.23 Precedence: list List-Id: "Development of GNU Guix and the GNU System distribution." List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , Errors-To: guix-devel-bounces+larch=yhetil.org@gnu.org Sender: "Guix-devel" X-Scanner: scn0 Authentication-Results: aspmx1.migadu.com; dkim=none; dmarc=none; spf=pass (aspmx1.migadu.com: domain of guix-devel-bounces@gnu.org designates 209.51.188.17 as permitted sender) smtp.mailfrom=guix-devel-bounces@gnu.org X-Spam-Score: 3.99 X-TUID: Mgj9Rl8TV6PL 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