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 GO3rInOveV8oUgAA0tVLHw (envelope-from ) for ; Sun, 04 Oct 2020 11:18:11 +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 0B7UHnOveV8gewAAbx9fmQ (envelope-from ) for ; Sun, 04 Oct 2020 11:18:11 +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 1FB2194023B for ; Sun, 4 Oct 2020 11:18:11 +0000 (UTC) Received: from localhost ([::1]:38870 helo=lists1p.gnu.org) by lists.gnu.org with esmtp (Exim 4.90_1) (envelope-from ) id 1kP21q-0000LJ-2B for larch@yhetil.org; Sun, 04 Oct 2020 07:18:10 -0400 Received: from eggs.gnu.org ([2001:470:142:3::10]:49680) by lists.gnu.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.90_1) (envelope-from ) id 1kP21i-0000Ko-Fo for bug-guix@gnu.org; Sun, 04 Oct 2020 07:18:02 -0400 Received: from debbugs.gnu.org ([209.51.188.43]:33483) by eggs.gnu.org with esmtps (TLS1.2:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.90_1) (envelope-from ) id 1kP21i-0004sc-6z for bug-guix@gnu.org; Sun, 04 Oct 2020 07:18:02 -0400 Received: from Debian-debbugs by debbugs.gnu.org with local (Exim 4.84_2) (envelope-from ) id 1kP21i-0006lj-3V for bug-guix@gnu.org; Sun, 04 Oct 2020 07:18:02 -0400 X-Loop: help-debbugs@gnu.org Subject: bug#43791: cuirass: Search queries are too slow. Resent-From: Mathieu Othacehe Original-Sender: "Debbugs-submit" Resent-CC: bug-guix@gnu.org Resent-Date: Sun, 04 Oct 2020 11:18:01 +0000 Resent-Message-ID: Resent-Sender: help-debbugs@gnu.org X-GNU-PR-Message: report 43791 X-GNU-PR-Package: guix X-GNU-PR-Keywords: To: 43791@debbugs.gnu.org X-Debbugs-Original-To: bug-guix@gnu.org Received: via spool by submit@debbugs.gnu.org id=B.160181025625980 (code B ref -1); Sun, 04 Oct 2020 11:18:01 +0000 Received: (at submit) by debbugs.gnu.org; 4 Oct 2020 11:17:36 +0000 Received: from localhost ([127.0.0.1]:45029 helo=debbugs.gnu.org) by debbugs.gnu.org with esmtp (Exim 4.84_2) (envelope-from ) id 1kP21I-0006ky-9G for submit@debbugs.gnu.org; Sun, 04 Oct 2020 07:17:36 -0400 Received: from lists.gnu.org ([209.51.188.17]:55864) by debbugs.gnu.org with esmtp (Exim 4.84_2) (envelope-from ) id 1kP21H-0006kr-4U for submit@debbugs.gnu.org; Sun, 04 Oct 2020 07:17:35 -0400 Received: from eggs.gnu.org ([2001:470:142:3::10]:49630) by lists.gnu.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.90_1) (envelope-from ) id 1kP21G-0000Ju-VK for bug-guix@gnu.org; Sun, 04 Oct 2020 07:17:34 -0400 Received: from fencepost.gnu.org ([2001:470:142:3::e]:40649) by eggs.gnu.org with esmtp (Exim 4.90_1) (envelope-from ) id 1kP21G-0004qb-LQ for bug-guix@gnu.org; Sun, 04 Oct 2020 07:17:34 -0400 Received: from [2a01:e0a:19b:d9a0:48f7:411c:55d8:6418] (port=51238 helo=cervin) by fencepost.gnu.org with esmtpsa (TLS1.2:RSA_AES_256_CBC_SHA1:256) (Exim 4.82) (envelope-from ) id 1kP21G-0000HU-5Q for bug-guix@gnu.org; Sun, 04 Oct 2020 07:17:34 -0400 From: Mathieu Othacehe Date: Sun, 04 Oct 2020 13:17:31 +0200 Message-ID: <87mu12p7ec.fsf@gnu.org> User-Agent: Gnus/5.13 (Gnus v5.13) Emacs/27.1 (gnu/linux) MIME-Version: 1.0 Content-Type: text/plain X-Spam-Score: -2.3 (--) X-BeenThere: debbugs-submit@debbugs.gnu.org X-Mailman-Version: 2.1.18 Precedence: list X-Spam-Score: -3.3 (---) X-BeenThere: bug-guix@gnu.org List-Id: Bug reports for GNU Guix List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , Errors-To: bug-guix-bounces+larch=yhetil.org@gnu.org Sender: "bug-Guix" X-Scanner: scn0 Authentication-Results: aspmx1.migadu.com; dkim=none; dmarc=pass (policy=none) header.from=gnu.org; spf=pass (aspmx1.migadu.com: domain of bug-guix-bounces@gnu.org designates 209.51.188.17 as permitted sender) smtp.mailfrom=bug-guix-bounces@gnu.org X-Spam-Score: -1.51 X-TUID: eOPwupVmfV48 Hello, Search queries can take a long time to complete. This query took 658.67 seconds to complete: --8<---------------cut here---------------start------------->8--- SELECT * FROM ( SELECT Builds.rowid, Builds.timestamp, Builds.starttime, Builds.stoptime, Builds.log, Builds.status, Builds.job_name, Builds.system, Builds.nix_name, Specifications.name FROM Builds INNER JOIN Evaluations ON Builds.evaluation = Evaluations.id INNER JOIN Specifications ON Evaluations.specification = Specifications.name WHERE (Builds.nix_name LIKE '%hurd-barebones.qcow2%') AND (0 IS NULL OR (Builds.status = 0)) AND ('guix-master' IS NULL OR (Specifications.name = 'guix-master')) AND ('x86_64-linux' IS NULL OR (Builds.system = 'x86_64-linux')) AND (NULL IS NULL OR (NULL < Builds.rowid)) AND (NULL IS NULL OR (NULL > Builds.rowid)) ORDER BY CASE WHEN NULL IS NULL THEN Builds.rowid ELSE -Builds.rowid END DESC LIMIT 1) ORDER BY rowid DESC 658.67 --8<---------------cut here---------------end--------------->8--- and this one: --8<---------------cut here---------------start------------->8--- SELECT MIN(Builds.rowid) FROM Builds INNER JOIN Evaluations ON Builds.evaluation = Evaluations.id INNER JOIN Specifications ON Evaluations.specification = Specifications.name WHERE (Builds.nix_name LIKE '%ganeti%') AND (NULL IS NULL OR (Builds.status = NULL)) AND (NULL IS NULL OR (Specifications.name = NULL)) AND (NULL IS NULL OR (Builds.system = NULL)) 146.70 --8<---------------cut here---------------end--------------->8--- took 146.70 seconds. The naive solution of adding an index on nix_name column does not work as it is still ignored for some reason. According to https://www.sqlite.org/optoverview.html, starting the LIKE search pattern with '%' prevent the index use. However, searching for 'hurd-barebones.qcow2%' also skips the index. Thanks, Mathieu