bug#43791: cuirass: Search queries are too slow.

bug#43791: cuirass: Search queries are too slow.

[Mathieu Othacehe]

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

bug#43791: cuirass: Search queries are too slow.

[Mathieu Othacehe]

> 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