Selecting indexes to use & NOT INDEXED

classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

Selecting indexes to use & NOT INDEXED

Mohit Sindhwani-5
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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Selecting indexes to use & NOT INDEXED

Puneet Kishor-2

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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Selecting indexes to use & NOT INDEXED

Igor Tandetnik
In reply to this post by Mohit Sindhwani-5
On 6/27/2011 11:18 AM, Mohit Sindhwani wrote:

> 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.

You can suppress the index on CAT with a unary plus operator, like this:
   ... AND +CAT=25;
--
Igor Tandetnik

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Selecting indexes to use & NOT INDEXED

Mohit Sindhwani-5
Hi Igor and Puneet,

On 27/6/2011 11:47 PM, Igor Tandetnik wrote:
> You can suppress the index on CAT with a unary plus operator, like this:
>     ... AND +CAT=25;

Thanks for the suggestions.  I'll try these :)

Best Regards,
Mohit.
28/6/2011 | 11:50 PM.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users