On Jun 27, 2011, at 11:18 AM, Mohit Sindhwani wrote:
> 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!
Try something like
SELECT *
FROM (
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
)
WHERE CAT=25;
You might have to prefix the columns with the appropriate table names (I can't
tell which table cat and the bounds are coming from). If the bounds are a part
of the Objects table, you could try
SELECT *
FROM (
SELECT * FROM Objects
WHERE minx <= 668632 + 250 AND maxx >= 668632 - 250
AND miny <= 1518661 + 250 AND maxy >= 1518661 - 250
) foo, Objects_Index
WHERE foo.Id = Objects_Index.id AND CAT=25;
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
