Hi,
Can anyone explain how does the below sql statmenent work,
select * from tbl1 where a=xx and b=yy; when a is indexed and b is indexed
seperately? I tried explain could not make out.
Does it select all records with t=111 and then do a search for b=222 with
in that set or other way?
>From the trace below i don;t see idx1 being used.
sqlite> create table test( t text,b text);
sqlite> create index idx1 on test(t);
sqlite> create index idx2 on test(b);
sqlite> insert into test values ('111','111');
sqlite> insert into test values ('111','222');
sqlite> insert into test values ('111','333');
sqlite> insert into test values ('222','111');
sqlite> insert into test values ('222','222');
sqlite> insert into test values ('222','333');
sqlite> pragma vdbe_trace=ON;
VDBE Execution Trace:
SQL: [pragma vdbe_trace=ON;]
0 Expire 1 0
1 Halt 0 0
sqlite> explain select * from test where t='111' and b='222';
0|Goto|0|25|
1|Integer|0|0|# test
2|OpenRead|0|2|
3|SetNumColumns|0|2|
4|Integer|0|0|# idx2
5|OpenRead|1|4|keyinfo(1,BINARY)
6|String8|0|0|222
7|IsNull|-1|22|
8|MakeRecord|1|0|a
9|MemStore|0|0|
10|MoveGe|1|22|
11|MemLoad|0|0|
12|IdxGE|1|22|+
13|IdxRowid|1|0|
14|MoveGe|0|0|
15|Column|0|0|# test.t
16|String8|0|0|111
17|Ne|353|21|collseq(BINARY)
18|Column|0|0|# test.t
19|Column|0|1|# test.b
20|Callback|2|0|
21|Next|1|11|
22|Close|0|0|
23|Close|1|0|
24|Halt|0|0|
25|Transaction|0|0|
26|VerifyCookie|0|3|
27|Goto|0|1|
28|Noop|0|0|
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
