I have an SQLite3 query which I have optimized to run very fast on a large data set (>20M records).

The actual query looks like this:

SELECT ROUND(F1, 4) AS F1, ROUND(F2, 4) AS F2
FROM (
    SELECT *
    FROM Table
    WHERE (F1 BETWEEN 1.05 AND 1.06)
        AND (F2 BETWEEN 3.74 AND 3.75)
) as filtered
JOIN time_ranges
ON t BETWEEN t0 AND t1
GROUP BY ROUND(F1, 4),  ROUND(F2, 4));

EXPLAN QUERY PLAN shows the following:

QUERY PLAN
|--SEARCH <Table> USING INDEX Index<TableF1F2> (F1>? AND F1<?)
|--SEARCH time_ranges USING COVERING INDEX trIndex (t0<?)
`--USE TEMP B-TREE FOR DISTINCT

The query is joining against a temp table (time_ranges) which has < 7000 rows and which took negligible time to create.

This query is running in my Android app:

SQLiteDatabase db = SQLite.DatabaseGet(ctx,database);
curs = db.rawQuery(query, null);
curs.moveToFirst();

It takes 7.009 seconds for the query to run, returning 314 results. Virtually the entire delay takes place in the call to curs.moveToFirst(). If I saw the same delay in executed it by other means I would think that db.rawQuery isn't actually executing the query at all but is deferring it until cur.moveToFirst() is called and then running very slowly.

HOWEVER - I have Termux install on the device and under Termux I have installed the sqlite3 command line shell. When I run the exact same query (with the temp table populated identically) under sqlite3 it takes 0.143 seconds and displays all 314 results without any noticeable delay.

Both methods are using the same database on the same device, only the access method is different. I am at a loss as to how the curs.moveToFirst() call in Java could take such a long time when the query is fast enough to seem instant from the command line.