Hello!
I was investigating the slowness of our /api/latestbuilds requests on
berlin.
I found that if we have just the two indexes currently defined in
‘schema.sql’, basically everything involves a table scan:
--8<---------------cut here---------------start------------->8---
sqlite> EXPLAIN QUERY PLAN select * from builds where system = "x";
0|0|0|SCAN TABLE builds
--8<---------------cut here---------------end--------------->8---
I tentatively defined new indexes that seem to help:
--8<---------------cut here---------------start------------->8---
sqlite> CREATE INDEX Builds_index_evaluation ON Builds(evaluation);
sqlite> CREATE INDEX Builds_index_status ON Builds(status);
sqlite> CREATE INDEX Builds_index_system ON Builds(system, evaluation);
sqlite> EXPLAIN QUERY PLAN select * from builds where evaluation = 12;
0|0|0|SEARCH TABLE builds USING INDEX Builds_index_evaluation (evaluation=?)
sqlite> EXPLAIN QUERY PLAN select * from builds where evaluation = 12 and
system ="x";
0|0|0|SEARCH TABLE builds USING INDEX Builds_index_system (system=? AND
evaluation=?)
sqlite> EXPLAIN QUERY PLAN select * from builds where evaluation = 12 and
status = 0;
0|0|0|SEARCH TABLE builds USING INDEX Builds_index_status (status=?)
--8<---------------cut here---------------end--------------->8---
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 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?
Thanks in advance! :-)
Ludo’.
