bug#43791: closed (cuirass: Search queries are too slow.)

[GNU bug Tracking System]

Your message dated Mon, 05 Oct 2020 15:02:27 +0200
with message-id <87r1qc7rmk.fsf@gnu.org>
and subject line Re: bug#43791: cuirass: Search queries are too slow.
has caused the debbugs.gnu.org bug report #43791,
regarding cuirass: Search queries are too slow.
to be marked as done.

(If you believe you have received this mail in error, please contact
help-debbugs@gnu.org.)


-- 
43791: http://debbugs.gnu.org/cgi/bugreport.cgi?bug=43791
GNU Bug Tracking System
Contact help-debbugs@gnu.org with problems
> --- Begin Message ---
>
>
>
> Subject: 
>
> cuirass: Search queries are too slow.
>
>
>
>
> Date: 
>
> Sun, 04 Oct 2020 13:17:31 +0200
>
>
>
>
> User-agent: 
>
> Gnus/5.13 (Gnus v5.13) Emacs/27.1 (gnu/linux)
>
>
>
>
> 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
>
>
>
> --- End Message ---
> --- Begin Message ---
>
>
>
> Subject: 
>
> Re: bug#43791: cuirass: Search queries are too slow.
>
>
>
>
> Date: 
>
> Mon, 05 Oct 2020 15:02:27 +0200
>
>
>
>
> User-agent: 
>
> Gnus/5.13 (Gnus v5.13) Emacs/27.1 (gnu/linux)
>
>
>
>
> > 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
>
>
> --- End Message ---