From unknown Fri Jun 20 07:22:39 2025 Content-Disposition: inline Content-Transfer-Encoding: quoted-printable MIME-Version: 1.0 X-Mailer: MIME-tools 5.509 (Entity 5.509) Content-Type: text/plain; charset=utf-8 From: bug#43791 <43791@debbugs.gnu.org> To: bug#43791 <43791@debbugs.gnu.org> Subject: Status: cuirass: Search queries are too slow. Reply-To: bug#43791 <43791@debbugs.gnu.org> Date: Fri, 20 Jun 2025 14:22:39 +0000 retitle 43791 cuirass: Search queries are too slow. reassign 43791 guix submitter 43791 Mathieu Othacehe severity 43791 normal thanks From debbugs-submit-bounces@debbugs.gnu.org Sun Oct 04 07:17:36 2020 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 To: bug-guix@gnu.org Subject: cuirass: Search queries are too slow. 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-Debbugs-Envelope-To: submit X-BeenThere: debbugs-submit@debbugs.gnu.org X-Mailman-Version: 2.1.18 Precedence: list List-Id: List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , Errors-To: debbugs-submit-bounces@debbugs.gnu.org Sender: "Debbugs-submit" X-Spam-Score: -3.3 (---) 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 From debbugs-submit-bounces@debbugs.gnu.org Mon Oct 05 09:02:49 2020 Received: (at 43791-done) by debbugs.gnu.org; 5 Oct 2020 13:02:49 +0000 Received: from localhost ([127.0.0.1]:48310 helo=debbugs.gnu.org) by debbugs.gnu.org with esmtp (Exim 4.84_2) (envelope-from ) id 1kPQ8e-0003Ly-Tz for submit@debbugs.gnu.org; Mon, 05 Oct 2020 09:02:49 -0400 Received: from eggs.gnu.org ([209.51.188.92]:34522) by debbugs.gnu.org with esmtp (Exim 4.84_2) (envelope-from ) id 1kPQ8c-0003Lf-Ik for 43791-done@debbugs.gnu.org; Mon, 05 Oct 2020 09:02:47 -0400 Received: from fencepost.gnu.org ([2001:470:142:3::e]:58162) by eggs.gnu.org with esmtp (Exim 4.90_1) (envelope-from ) id 1kPQ8W-0001w6-S6 for 43791-done@debbugs.gnu.org; Mon, 05 Oct 2020 09:02:41 -0400 Received: from [2a01:e0a:19b:d9a0:4d1:e5e4:e52d:9d3f] (port=51548 helo=cervin) by fencepost.gnu.org with esmtpsa (TLS1.2:RSA_AES_256_CBC_SHA1:256) (Exim 4.82) (envelope-from ) id 1kPQ8L-0004Vt-7K for 43791-done@debbugs.gnu.org; Mon, 05 Oct 2020 09:02:32 -0400 From: Mathieu Othacehe To: 43791-done@debbugs.gnu.org Subject: Re: bug#43791: cuirass: Search queries are too slow. References: <87mu12p7ec.fsf@gnu.org> Date: Mon, 05 Oct 2020 15:02:27 +0200 In-Reply-To: <87mu12p7ec.fsf@gnu.org> (Mathieu Othacehe's message of "Sun, 04 Oct 2020 13:17:31 +0200") Message-ID: <87r1qc7rmk.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-Debbugs-Envelope-To: 43791-done X-BeenThere: debbugs-submit@debbugs.gnu.org X-Mailman-Version: 2.1.18 Precedence: list List-Id: List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , Errors-To: debbugs-submit-bounces@debbugs.gnu.org Sender: "Debbugs-submit" X-Spam-Score: -3.3 (---) > However, searching for 'hurd-barebones.qcow2%' also skips the index. That's because the index was not created in case insensitive mode. I dropped support for searching with "^" and "$" characters as this is not compatible with using an index and hence way too slow. The new search behaviour is to remove any "%" that could be added by the user, and add one at the end of the query. It should be enough for the modest search needs in Cuirass and reasonable in term of performance (< 10 ms). Anyway, fixed with: 930c2f315c6a9768d31a80d35d2a2550b588deaa. Thanks, Mathieu From unknown Fri Jun 20 07:22:39 2025 Received: (at fakecontrol) by fakecontrolmessage; To: internal_control@debbugs.gnu.org From: Debbugs Internal Request Subject: Internal Control Message-Id: bug archived. Date: Tue, 03 Nov 2020 12:24:09 +0000 User-Agent: Fakemail v42.6.9 # This is a fake control message. # # The action: # bug archived. thanks # This fakemail brought to you by your local debbugs # administrator