0

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.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
iggy
  • 1,613
  • 1
  • 18
  • 35
  • The version of the source was rather old (3.7.2) versus (3.8.2) on the desktop and for some reason the query planner wasn't doing its job right in 3.7.2 – iggy Dec 28 '13 at 00:35
  • Don't you think using an **rtree** and naming it **kdtree** is misleading? – Has QUIT--Anony-Mousse Dec 28 '13 at 13:00
  • @Anony-Mousse in my use case I am storing points instead of ranges and in this case rtree is a kdtree. But thanks for pointing it out, I guess it might be misleading for others – iggy Jan 02 '14 at 23:48
  • No it is not and kd-tree then. Read up on kd-trees and R-trees. R-trees have all data in the leaves, kd-trees don't; and R-trees are a block oriented data structure, kd-trees are not... – Has QUIT--Anony-Mousse Jan 03 '14 at 08:45

1 Answers1

0

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

Antonin
  • 149
  • 1