sqlite not using index on android

Question

I am executing the following query on an sqlite database on desktop and android:

SELECT kdtree_nonendpt.verid, minlat, minlon, data
FROM kdtree_nonendpt, kdtree_nonendpt_data
WHERE kdtree_nonendpt.verid IN (SELECT kdtree_nonendpt.verid
                                FROM kdtree_nonendpt
                                WHERE minlat>=? AND maxlat<=? AND minlon>=? AND maxlon<=?
                               ) AND
      kdtree_nonendpt.verid = kdtree_nonendpt_data.verid

This is the structure of data

.schema kdtree_nonendpt
CREATE VIRTUAL TABLE kdtree_nonendpt USING rtree(verid UNSIGNED INT PRIMARY KEY,minlat REAL, maxlat REAL, minlon REAL, maxlon REAL); 

.schema kdtree_nonendpt_data
CREATE TABLE kdtree_nonendpt_data(verid UNSIGNED INT PRIMARY KEY,data TEXT);
CREATE INDEX kdtree_nonendpt_data_idx ON kdtree_nonendpt_data(verid);

This is the result of EXPLAIN QUERY above on android

0 0 1 SCAN TABLE kdtree_nonendpt_data (~31805 rows)
0 1 0 SCAN TABLE kdtree_nonendpt VIRTUAL TABLE INDEX 1: (~0 rows)
0 0 0 EXECUTE LIST SUBQUERY 1
1 0 0 SCAN TABLE kdtree_nonendpt VIRTUAL TABLE INDEX 2:DaBbDcBd (~0 rows)

And this is the result for desktop

0 0 0 SCAN TABLE kdtree_nonendpt VIRTUAL TABLE INDEX 1: (~0 rows)
0 0 0 EXECUTE LIST SUBQUERY 1
1 0 0 SCAN TABLE kdtree_nonendpt VIRTUAL TABLE INDEX 2:DaBbDcBd (~0 rows)
0 1 1 SEARCH TABLE kdtree_nonendpt_data USING INDEX sqlite_autoindex_kdtree_nonendpt_data_1 (verid=?) (~1 rows)

The sqlite on the desktop was installed using apt-get and the one on android was compiled with the following options from source:

LOCAL_MODULE:= sp_sqlite
LOCAL_CPPFLAGS := -std=c++0x -fexceptions -Werror -DNDEBUG=1 -DDEBUG=0 -O3 -DANDROID -frtti -DSQLITE_ENABLE_RTREE=1 -DSQLITE_ENABLE_STAT4=1 -DSQLITE_ENABLE_FTS3 -DSQLITE_ENABLE_FTS3_PARENTHESIS -DSQLITE_ENABLE_FTS4=1 -DSQLITE_THREADSAFE=2

What could be the reason that sqlite doesn't use the index on android? The row verid is a PRIMARY KEY in both tables so there shouldn't be any scan except for the scanning done in the subquery for rtree.

Answer 1

I don't think the subquery is needed. Try this - the query planner might work better:

SELECT kdtree_nonendpt.verid, minlat, minlon, data

FROM kdtree_nonendpt, kdtree_nonendpt_data

WHERE minlat>=? AND maxlat<=?

AND minlon>=? AND maxlon<=?

AND kdtree_nonendpt.verid = kdtree_nonendpt_data.verid