Tomash Brechko <[EMAIL PROTECTED]> wrote:
>
> sqlite> .explain
> sqlite> explain SELECT * FROM People where GUID in ("ABC", "RDT", "TUV");
> addr opcode p1 p2 p3
> ---- -------------- ---------- ---------- -------------------
> ....
> 2 OpenRead 0 6
> ....
> 5 MemLoad 0 0
> ....
> 8 OpenEphemeral 1 0 keyinfo(1,BINARY)
> 9 SetNumColumns 1 1
> 10 String8 0 0 ABC
> 11 MakeRecord 1 0 b
> 12 IdxInsert 1 0
> 13 String8 0 0 RDT
> 14 MakeRecord 1 0 b
> 15 IdxInsert 1 0
> 16 String8 0 0 TUV
> 17 MakeRecord 1 0 b
> 18 IdxInsert 1 0
> ....
> 26 Found 1 28
> ....
> 30 Callback 1 0
> 31 Next 0 5
> ....
>
>
> See, at instruction 8 a temporary table (or rather index) is created,
> and values "ABC", "RDT", "TUV" are put into it, so that we can later
> use logarithmic search instead of liner search as would be done with
> OR chain.
>
> The problem is that jump at instruction 31 (Next) brings us back to
> instruction 5. This means that the query plan is:
>
> for every row in People do
> create temp table
> populate temp table
> see if current value from People is in temp table
> next
>
> This doesn't look right.
Your interpretation of what is happening isn't right. Look more
closely at instructions 5 through 7:
5 MemLoad 0 0
6 If 0 19
7 MemInt 1 0
Memory location 0 is being used as a flag that indicates whether
or not the temporary table has been initialized. Instructions
5 and 6 test this flag and skip the initializion if it has
already been done. Instruction 7 sets the flag at the beginning
of the initialization process.
So the pseudocode is really like this:
for every row in People do
if temp table is uninitialized
create temp table
populate temp table
endif
....
endfor
For additional insight, use "PRAGMA vdbe_trace=ON" before running
the query and see that the temp table initializatio is only done
once.
--
D. Richard Hipp <[EMAIL PROTECTED]>
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
