Hello,
> SELECT E.id, E.status, E.timestamp, E.checkouttime, E.evaltime, B.total, > B.succeeded, B.failed, B.scheduled FROM (SELECT id, status, timestamp, > checkouttime, evaltime FROM Evaluations WHERE (id='8255' )) E LEFT JOIN > (SELECT rowid, evaluation, SUM(status=0) as succeeded, SUM(status>0) as > failed, SUM(status<0) as scheduled, SUM(status>-100) as total FROM Builds > GROUP BY evaluation) B ON B.evaluation=E.id ORDER BY E.id ASC 86.23 Turns out those queries are really slow because they are doing full scans of the Builds table. Using "EXPLAIN QUERY PLAN" shows that this is caused by the "GROUP BY" in the "B" sub-query. Running the above query takes around 3 seconds right now. I think that it took 86.23 seconds because the WAL file contained a lot of temporary data, slowing down the whole query. I ran a "PRAGMA wal_checkpoint(FULL)" as those queries were started to slow down and observed an immediate improvement. Anyway, the real problem here is just highlighted by the WAL file usage. Those queries can really go faster. I rewrote them and added a few indexes with 0ffcb80ebbaa2b177f03548035a2ef21ae7ac41d. They now take less than 1ms, which should very much improve the web browsing experience as well as decrease the database worker starvation. Thanks, Mathieu -- https://othacehe.org
