Re: [sqlite] How to use "IN" keyword for multi-column index
> 2. I tried this first: [ select * from map where (x=1 and y=1) or (x=1 and > y=2) or (x=1 and y=3) ] but that didn't use the index -- not on 3.5.6 anyway AFAIK "OR" will always omit indexes, this is why I am trying to use "IN" -- Alexander Batyrshin aka bash bash = Biomechanica Artificial Sabotage Humanoid ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to use "IN" keyword for multi-column index
On Thu, May 22, 2008 at 2:02 PM, Igor Tandetnik <[EMAIL PROTECTED]> wrote: > IN only works on a single column. The closest you can get to this is > something like > > SELECT map.* > FROM map join ( > select 1 x, 1 y > union all > select 1 x, 2 y > union all > select 1 x, 3 y) t > ON map.x = t.x AND map.y=t.y; > > I checked - it does use map_xy index. The subselect in parentheses > essentially creates a temporary table, which is then joined with your > main table. > > Igor Tandetnik Hah! I was going to test something similar out, but decided not to. I figured that that would never actually work. That'll teach me to make assumptions :) This is what you'd proposed: sqlite> explain query plan select * from map m join (select 1 as x, 1 as y union all select 1, 2 union all select 1, 3) z on z.x=m.x and z.y=m.y; orde from deta - 0 1 TABLE AS z 1 0 TABLE map AS m WITH INDEX map_xy And this is what I considered: sqlite> explain query plan select * from map where x=1 and y=1 union all select * from map where x=1 and y=2 union all select * from map where x=1 and y=3; orde from deta - 0 0 TABLE map WITH INDEX map_xy 0 0 TABLE map WITH INDEX map_xy 0 0 TABLE map WITH INDEX map_xy -- -- Stevie-O Real programmers use COPY CON PROGRAM.EXE ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to use "IN" keyword for multi-column index
> IN only works on a single column. The closest you can get to this is > something like > > SELECT map.* > FROM map join ( > select 1 x, 1 y > union all > select 1 x, 2 y > union all > select 1 x, 3 y) t > ON map.x = t.x AND map.y=t.y; Thanks. I will use more than 3 keys, so I will create temporary memory table with keys for this stuff. -- Alexander Batyrshin aka bash bash = Biomechanica Artificial Sabotage Humanoid ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to use "IN" keyword for multi-column index
On Thu, May 22, 2008 at 1:41 PM, Alexander Batyrshin <[EMAIL PROTECTED]>
wrote:
> Hello All,
> For example we have table like this:
>
> CREATE TABLE map (
> name text,
> x integer,
> y integer
> );
> CREATE INDEX map_xy ON map(x,y);
>
> How to query this table with "IN" keyword?
> Query like this, doesn't work:
>
> SELECT * FROM map WHERE (x,y) IN ((1,1),(1,2),(1,3));
select * from map where x=1 and y in (1,2,3);
1. Some people might suggest some crazy things like [ SELECT * FROM map
WHERE x||'.'||y in ('1.1','1.2','1.3') ]. While this would technically work,
it wouldn't be able to use your index.
2. I tried this first: [ select * from map where (x=1 and y=1) or (x=1 and
y=2) or (x=1 and y=3) ] but that didn't use the index -- not on 3.5.6 anyway
3. If you're going to have a bunch of choices with different values for X
and Y, you *might* want to creating a precomputed statement of the form
'select * from map where x=? and y=?', then binding and re-executing the
statement for each (x,y) pair you're interested in, and piece them together
in your application. You wouldn't be able to take advantage of ORDER BY,
GROUP BY, or DISTINCT that way, but it would work.
--
-- Stevie-O
Real programmers use COPY CON PROGRAM.EXE
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to use "IN" keyword for multi-column index
Alexander Batyrshin <[EMAIL PROTECTED]> wrote: > Hello All, > For example we have table like this: > > CREATE TABLE map ( > name text, > x integer, > y integer > ); > CREATE INDEX map_xy ON map(x,y); > > How to query this table with "IN" keyword? > Query like this, doesn't work: > > SELECT * FROM map WHERE (x,y) IN ((1,1),(1,2),(1,3)); IN only works on a single column. The closest you can get to this is something like SELECT map.* FROM map join ( select 1 x, 1 y union all select 1 x, 2 y union all select 1 x, 3 y) t ON map.x = t.x AND map.y=t.y; I checked - it does use map_xy index. The subselect in parentheses essentially creates a temporary table, which is then joined with your main table. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
