Hi All,
Sorry for the long email, but the background is probably needed :)
1. We have a geographical query that is supported by an R-Tree index. A
join is required to filter by category while restricting by the bounding
box. On the main table (objects), we have an index IDX_OBJ_CAT on
Objects(cat) and we have a virtual index on the RTree table (Objects_Index).
We felt that the query was a bit slow:
select * FROM Objects, Objects_Index
WHERE Objects.id = Objects_Index.id
AND minx <= 668632 + 250 AND maxx >= 668632 - 250
AND miny <= 1518661 + 250 AND maxy >= 1518661 - 250
AND CAT=25;
Doing an explain query plan revealed that SQlite was scanning Objects
with the IDX_OBJ_CAT first and then using the R-Tree. Without
restricting by CAT, we found the query was much faster and was using
only the virtual R-Tree index.
We wanted to get SQLite to use the R-Tree first, so we tried a few
different things - eventually, we killed the IDX_OBJ_CAT index and the
query became (yes, wait for it) almost 100x faster!
So, now we want to go about fixing our schema and queries to benefit
from this.
What do you recommend? What would be the best way to make SQLite3 use
the R-Tree? We're a bit unsure about dropping the category index since
we are not sure which other queries will be affected.
2. While searching around, I found INDEXED BY and NOT INDEXED and NOT
INDEXED worked very well for this query. But, an email from 2009 said:
"many are of the opinion that this feature is prone to misuse."
> There is the "INDEXED BY" clause. But many are of the opinion that
> this feature is prone to misuse.
>
> http://www.sqlite.org/lang_indexedby.html
>
> Dan.
What's the current wisdom?
Thanks,
Mohit.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
