Hi Björn, Björn Höfling <bjoern.hoefl...@bjoernhoefling.de> skribis:
> The link you provided explains it: The column over which you are sorting > (stoptime) is not indexed. Add it to the (same) index: > > sqlite> DROP INDEX Builds_index_evaluation; > sqlite> CREATE INDEX Builds_index_evaluation ON Builds(evaluation, stoptime); > sqlite> EXPLAIN QUERY PLAN select * from builds where evaluation = 12 and > status > 0 order by stoptime ; > 0|0|0|SEARCH TABLE builds USING INDEX Builds_index_evaluation (evaluation=?) > > If there is more SQL-trouble, I can try to help out. Indeed, that solves the problem for this simple example, thanks! Now, if I go back to the big query that /api/latestbuilds makes¹, the result is still pretty bad: --8<---------------cut here---------------start------------->8--- sqlite> EXPLAIN QUERY PLAN SELECT * FROM ( ...> SELECT Builds.derivation, 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 (:id IS NULL OR (:id = Builds.rowid)) ...> AND (:derivation IS NULL OR (:derivation = Builds.derivation)) ...> AND (:jobset IS NULL OR (:jobset = Specifications.name)) ...> AND (:job IS NULL OR (:job = Builds.job_name)) ...> AND (:system IS NULL OR (:system = Builds.system)) ...> AND (:evaluation IS NULL OR (:evaluation = Builds.evaluation)) ...> AND (:status IS NULL OR (:status = 'done' AND Builds.status >= 0) ...> OR (:status = 'pending' AND Builds.status < 0) ...> OR (:status = 'succeeded' AND Builds.status = 0) ...> OR (:status = 'failed' AND Builds.status > 0)) ...> AND (:borderlowtime IS NULL OR :borderlowid IS NULL ...> OR ((:borderlowtime, :borderlowid) < (Builds.stoptime, Builds.rowid))) ...> AND (:borderhightime IS NULL OR :borderhighid IS NULL ...> OR ((:borderhightime, :borderhighid) > (Builds.stoptime, Builds.rowid))) ...> ORDER BY ...> CASE WHEN :borderlowtime IS NULL ...> OR :borderlowid IS NULL THEN Builds.stoptime ...> ELSE -Builds.stoptime ...> END DESC, ...> CASE WHEN :borderlowtime IS NULL ...> OR :borderlowid IS NULL THEN Builds.rowid ...> ELSE -Builds.rowid ...> END DESC ...> LIMIT :nr) ...> ORDER BY stoptime, rowid ASC; 1|0|0|SCAN TABLE Builds 1|1|1|SEARCH TABLE Evaluations USING INTEGER PRIMARY KEY (rowid=?) 1|2|2|SEARCH TABLE Specifications USING COVERING INDEX sqlite_autoindex_Specifications_1 (name=?) 1|0|0|USE TEMP B-TREE FOR ORDER BY 0|0|0|SCAN SUBQUERY 1 0|0|0|USE TEMP B-TREE FOR ORDER BY --8<---------------cut here---------------end--------------->8--- I don’t really know what additional index to create (and I’d rather let SQLite do it for me, if it were possible). Thoughts? Thanks, Ludo’. ¹ https://git.savannah.gnu.org/cgit/guix/guix-cuirass.git/tree/src/cuirass/database.scm#n550
