Collector count query needs optimizing

[ross-spencer]

The following:

SELECT address, COUNT(*) AS total_count, SUM(CASE WHEN datetime(date_time) >= datetime('now', '-1 day') THEN 1 ELSE 0 END) AS count_24hr FROM data_points GROUP BY address ORDER BY total_count DESC;

Was introduced to include daily counts. It is taking a lot longer than the previous call, and for every connection to the database this is adding up.

We can:

1. either optimize the query,
2. optimize the python.

[ross-spencer]

A complexity here is there does seem to be some caching going on when testing locally.

We can get some timings through (.timer on), e.g. orig:

Run Time: real 0.018 user 0.014927 sys 0.002855

new:

Run Time: real 0.394 user 0.142779 sys 0.250545

Size of local DB:

180356
Run Time: real 0.002 user 0.000993 sys 0.000984

[ross-spencer]

Current server timings:

Run Time: real 248.644 user 9.503630 sys 46.506179

[ross-spencer]

Looks like an index might help, adding:

CREATE INDEX data_points_dt_addr on data_points(address, date_time);