SQLite sqlite3_step() hangs with big database
Looking up rows by their timestamp value can be optimized with an index on this column:
CREATE INDEX whatever ON ExampleTable(timestamp);And this query is inefficient: ORDER BY does not affect values that are averaged, and the timestamp values in B and C are always identical, so you can drop one of them:
SELECT ROUND(AVG(difference), 5) AS distance FROM ( SELECT timestamp - (SELECT MAX(timestamp) FROM ExampleTable AS B WHERE timestamp < A.timestamp) AS difference FROM ExampleTable AS A)
I eventually went with this solution:
CREATE TABLE tmp AS SELECT timestamp FROM ExampleTable ORDER BY timestampSELECT ROUND(AVG(difference), 5) FROM ( SELECT ( SELECT A.timestamp - B.timestamp FROM tmp as B WHERE B.rowid = A.rowid-1 ) as difference FROM tmp as A ORDER BY timestamp)DROP TABLE ExampleTableActually I went further and I am only using this strategy for high number of rows (> 40k), since the other strategy (single query) works better for "small" tables.