On Sat, 10 Nov 2018 18:33:23 +0100 l...@gnu.org (Ludovic Courtès) wrote: > Now, ‘db-get-builds’ in Cuirass uses a more complex query. In > particular, it orders things, very roughly along these lines: > > --8<---------------cut here---------------start------------->8--- > sqlite> EXPLAIN QUERY PLAN select * from builds where evaluation = 12 > sqlite> and status > 0 order by stoptime ; > 0|0|0|SEARCH TABLE builds USING INDEX Builds_index_evaluation > (evaluation=?) 0|0|0|USE TEMP B-TREE FOR ORDER BY > --8<---------------cut here---------------end--------------->8--- > > I’m pretty much a database newbie so please forgive the naive > question, but is there something we can do to avoid this extra B-tree > step, which seems costly in space and time? > <http://www.sqlite.com/matrix/eqp.html> suggests it’s just a matter > of adding yet another index but I couldn’t get that. > > Anything else we should do?
The link you provided explains it: The column over which you are sorting (stoptime) is not indexed. Add it to the (same) index: --8<---------------cut here---------------start------------->8--- 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=?) --8<---------------cut here---------------end--------------->8--- If there is more SQL-trouble, I can try to help out. Björn
pgp9QgN3Tz2nD.pgp
Description: OpenPGP digital signature
