Re: [Cuirass] Missing database indexes?

[Björn Höfling]

On Sat, 10 Nov 2018 18:33:23 +0100
address@hidden (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

pgpH3dLwrG5eC.pgp
Description: OpenPGP digital signature