SCAN in the query plan = Rewind...Next LOOP in opcodes
SEARCH in the query plan = Column...Seek in opcodes
SQLite has determined that creating an automatic index on the referenced tables
should be faster than performing a full table scan for the general case.
asql> explain query plan select * from em,idv,mny,lo where em.name=lo.name and
idv.id=mny.id;
sele order from deta
-
0 0 0 SCAN TABLE em (~100 rows)
0 1 3 SEARCH TABLE lo USING INDEX sqlite_autoindex_lo_1
(name=?) (~1 rows)
0 2 1 SCAN TABLE idv (~100 rows)
0 3 2 SEARCH TABLE mny USING INDEX sqlite_autoindex_mny_1
(id=?) (~1 rows)
asql> explain select * from em,idv,mny,lo where em.name=lo.name and
idv.id=mny.id;
addr opcode p1p2p3p4 p5 comment
- - -- -
0 Trace 0 0 000 NULL
1 Goto 0 42000 NULL
2 OpenRead 0 2 1 2 00 em
3 OpenRead 3 8 1 2 00 lo
4 OpenRead 4 9 1 Keyinfo(1,BINARY) 00
sqlite_autoindex_lo_1
5 OpenRead 1 4 1 2 00 idv
6 OpenRead 2 6 1 2 00 mny
7 OpenRead 5 7 1 Keyinfo(1,BINARY) 00
sqlite_autoindex_mny_1
8 Rewind 0 35000 NULL
9 Column 0 0 100 em.name
10IsNull 1 34000 NULL
11Affinity 1 1 0 d 00 NULL
12SeekGe 4 341 1 00 NULL
13IdxGE 4 341 1 01 NULL
14IdxRowid 4 2 000 NULL
15Seek 3 2 000 NULL
16Rewind 1 34000 NULL
17Column 1 0 300 idv.id
18IsNull 3 33000 NULL
19Affinity 3 1 0 d 00 NULL
20SeekGe 5 333 1 00 NULL
21IdxGE 5 333 1 01 NULL
22IdxRowid 5 4 000 NULL
23Seek 2 4 000 NULL
24Column 0 0 500 em.name
25Column 0 1 600 em.age
26Column 1 0 700 idv.id
27Column 1 1 800 idv.name
28Column 5 0 900 mny.id
29Column 2 1 10 00 mny.sal
30Column 4 0 11 00 lo.name
31Column 3 1 12 00 lo.addr
32ResultRow 5 8 000 NULL
33Next 1 17001 NULL
34Next 0 9 001 NULL
35Close 0 0 000 NULL
36Close 3 0 000 NULL
37Close 4 0 000 NULL
38Close 1 0 000 NULL
39Close 2 0 000 NULL
40Close 5 0 000 NULL
41Halt 0 0 000 NULL
42Transaction1 0 000 NULL
43VerifyCookie 1 4 000 NULL
44TableLock 1 2 0 em 00 NULL
45TableLock 1 8 0 lo 00 NULL
46TableLock 1 4 0 idv00 NULL
47TableLock 1 6 0 mny00 NULL
48Goto 0 2 000 NULL
-Ursprüngliche Nachricht-
Von: Sairam Gaddam [mailto:gaddamsai...@gmail.com]
Gesendet: Montag, 02. Februar 2015 16:24
An: sqlite-users@sqlite.org
Betreff: [sqlite] regarding looping in vdbe for sqlite table joins!
Normally for executing joins in sqlite,the vdbe program opens 1 loop for each
and every table but in my code(attached that file via pastebin) ,i am facing an
issue because it is opening only 2 loops even if i use 4 tables in joining
operation.
can anyone explain why it happened like that and loops for which tables got
opened?
code : http://pastebin.com/PHV4K4Hh
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, H
