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
