[sqlite] SELECT WHERE with RTREE and second condition slow

[Alessandro Furieri]

Hi List,

as it recently emerged from a discussion on the SQLite-Users
mailing list [1] there is a very useful option effectively helping
in writing efficient and fast spatial queries based
on the R*Tree Spatial Index.

[1] https://www.mail-archive.com/sqlite...@mailinglists.sqlite.org/msg103768.html

there is no "magic trick", it's a well documented
feature [2] of SQLite that I personally ignored
before today.

[2] http://sqlite.org/optoverview.html#uplus

short summary:

1. a query based on some WHERE clause intended to
   filter at the same time both the R*Tree and
   some other ordinary column(s) could easily
   end up in a very sluggish execution.
   this usually happens when the non-spatial 
   column is supported by an index; in this case
   SQLite will always adopt a query plan based
   on the ordinary index thus completely ignoring
   the R*TREE.
  
2. anyway the fancy "Unary +" syntax allows to
   instruct the query planner so to ignore an
   eventual index supporting a column declared
   in the context of a WHERE clause, and this will
   effectively preserve the R*TREE from unexpected
   interferences.
  
Really, very interesting.

bye Sandro