On 8/29/19 9:19 PM, Keith Medcalf wrote: > Fascinating ... because the optimal query plan would be to do a table scan of > tbl1 and then dip into tbl2 for each row to get the update value. The only > meaningful index then would be on tbl2(int) (that is idx3) since an index > will be unhelpful for the LIKE constraint.
I noticed that, too. But I stopped wondering about optimizer internals,
back when web-browsers started to JIT compile javascript ;)
> Is the generated code different for the two queries, the one that runs slow
> -vs- the one that runs fast? (That is, the output from EXPLAIN ..., not the
> output from EXPLAIN QUERY PLAN, or from .eqp full in the CLI).
This is the output of EXPLAIN UPDATE before I recreate the index:
> 0 Init 0 73 0 00
> 1 Null 0 4 5 00
> 2 OpenRead 0 13 0 0 00
> 3 Rewind 0 7 0 00
> 4 Rowid 0 5 0 00
> 5 RowSetAdd 4 5 0 00
> 6 Next 0 4 0 01
> 7 OpenWrite 0 13 0 7 00
> 8 OpenWrite 1 18 0 k(2,,) 00
> 9 RowSetRead 4 72 5 00
> 10 NotExists 0 9 5 00
> 11 Rowid 0 6 0 00
> 12 Null 0 7 0 00
> 13 Null 0 8 0 00
> 14 Column 0 3 9 00
> 15 Null 0 10 0 00
> 16 Null 0 11 0 00
> 17 Null 0 12 0 00
> 18 Copy 5 13 0 00
> 19 Null 0 14 0 00
> 20 Column 0 1 15 00
> 21 Column 0 2 16 00
> 22 Null 0 22 22 00
> 23 Noop 5 3 0 00
> 24 Integer 1 23 0 00
> 25 OpenRead 4 11 0 3 00
> 26 OpenRead 6 23 0 k(2,,) 00
> 27 Column 0 2 24 00
> 28 Affinity 24 1 0 D 00
> 29 SeekLE 6 39 24 1 00
> 30 DeferredSeek 6 0 4 00
> 31 Column 4 1 29 00
> 32 Concat 29 28 26 00
> 33 Column 0 1 27 00
> 34 Function0 0 26 25 like(2) 02
> 35 IfNot 25 38 1 00
> 36 IdxRowid 6 22 0 00
> 37 DecrJumpZero 23 39 0 00
> 38 Prev 6 30 0 00
> 39 SCopy 22 17 0 00
> 40 Column 0 4 18 NULL 00
> 41 Column 0 5 19 NULL 00
> 42 Column 0 6 20 0 00
> 43 Affinity 14 7 0 DBDDBDD 00
> 44 SCopy 17 2 0 00
> 45 IntCopy 13 3 0 00
> 46 MakeRecord 2 2 1 00
> 47 MakeRecord 14 7 21 00
> 48 FkIfZero 0 56 0 00
> 49 IsNull 9 56 0 00
> 50 SCopy 9 25 0 00
> 51 MustBeInt 25 55 0 00
> 52 OpenRead 7 11 0 3 00
> 53 NotExists 7 55 25 00
> 54 Goto 0 56 0 00
> 55 FkCounter 0 -1 0 00
> 56 Close 7 0 0 00
> 57 Column 0 3 26 00
> 58 Rowid 0 27 0 00
> 59 IdxDelete 1 26 2 00
> 60 Delete 0 68 13 tbl1 00
> 61 IsNull 17 68 0 00
> 62 SCopy 17 25 0 00
> 63 MustBeInt 25 67 0 00
> 64 OpenRead 8 11 0 3 00
> 65 NotExists 8 67 25 00
> 66 Goto 0 68 0 00
> 67 FkCounter 0 1 0 00
> 68 Close 8 0 0 00
> 69 IdxInsert 1 1 2 2 00
> 70 Insert 0 21 13 tbl1 05
> 71 Goto 0 9 0 00
> 72 Halt 0 0 0 00
> 73 Transaction 0 1 20 0 01
> 74 String8 0 28 0 % 00
> 75 Goto 0 1 0 00
... and that is the output afterwards:
> 0 Init 0 73 0 00
> 1 Null 0 4 5 00
> 2 OpenRead 0 13 0 0 00
> 3 Rewind 0 7 0 00
> 4 Rowid 0 5 0 00
> 5 RowSetAdd 4 5 0 00
> 6 Next 0 4 0 01
> 7 OpenWrite 0 13 0 7 00
> 8 OpenWrite 2 18 0 k(2,,) 00
> 9 RowSetRead 4 72 5 00
> 10 NotExists 0 9 5 00
> 11 Rowid 0 6 0 00
> 12 Null 0 7 0 00
> 13 Null 0 8 0 00
> 14 Column 0 3 9 00
> 15 Null 0 10 0 00
> 16 Null 0 11 0 00
> 17 Null 0 12 0 00
> 18 Copy 5 13 0 00
> 19 Null 0 14 0 00
> 20 Column 0 1 15 00
> 21 Column 0 2 16 00
> 22 Null 0 22 22 00
> 23 Noop 5 3 0 00
> 24 Integer 1 23 0 00
> 25 OpenRead 4 11 0 3 00
> 26 OpenRead 6 23 0 k(2,,) 00
> 27 Column 0 2 24 00
> 28 Affinity 24 1 0 D 00
> 29 SeekLE 6 39 24 1 00
> 30 DeferredSeek 6 0 4 00
> 31 Column 4 1 29 00
> 32 Concat 29 28 26 00
> 33 Column 0 1 27 00
> 34 Function0 0 26 25 like(2) 02
> 35 IfNot 25 38 1 00
> 36 IdxRowid 6 22 0 00
> 37 DecrJumpZero 23 39 0 00
> 38 Prev 6 30 0 00
> 39 SCopy 22 17 0 00
> 40 Column 0 4 18 NULL 00
> 41 Column 0 5 19 NULL 00
> 42 Column 0 6 20 0 00
> 43 Affinity 14 7 0 DBDDBDD 00
> 44 SCopy 17 2 0 00
> 45 IntCopy 13 3 0 00
> 46 MakeRecord 2 2 1 00
> 47 MakeRecord 14 7 21 00
> 48 FkIfZero 0 56 0 00
> 49 IsNull 9 56 0 00
> 50 SCopy 9 25 0 00
> 51 MustBeInt 25 55 0 00
> 52 OpenRead 7 11 0 3 00
> 53 NotExists 7 55 25 00
> 54 Goto 0 56 0 00
> 55 FkCounter 0 -1 0 00
> 56 Close 7 0 0 00
> 57 Column 0 3 26 00
> 58 Rowid 0 27 0 00
> 59 IdxDelete 2 26 2 00
> 60 Delete 0 68 13 tbl1 00
> 61 IsNull 17 68 0 00
> 62 SCopy 17 25 0 00
> 63 MustBeInt 25 67 0 00
> 64 OpenRead 8 11 0 3 00
> 65 NotExists 8 67 25 00
> 66 Goto 0 68 0 00
> 67 FkCounter 0 1 0 00
> 68 Close 8 0 0 00
> 69 IdxInsert 2 1 2 2 00
> 70 Insert 0 21 13 tbl1 05
> 71 Goto 0 9 0 00
> 72 Halt 0 0 0 00
> 73 Transaction 0 1 22 0 01
> 74 String8 0 28 0 % 00
> 75 Goto 0 1 0 00
Indeed, they are not identical:
> $ diff explain{1,2}
> 9c9
> < > 8 OpenWrite 1 18 0 k(2,,) 00
> ---
>> > 8 OpenWrite 2 18 0 k(2,,) 00
> 60c60
> < > 59 IdxDelete 1 26 2 00
> ---
>> > 59 IdxDelete 2 26 2 00
> 70c70
> < > 69 IdxInsert 1 1 2 2 00
> ---
>> > 69 IdxInsert 2 1 2 2 00
> 74c74
> < > 73 Transaction 0 1 20 0 01
> ---
>> > 73 Transaction 0 1 22 0 01
> Also, what version of SQLite3 are you using?
> SELECT sqlite_version();
returns
> 3.29.0
Packages installed on my dev machine:
> $ dpkg-query -l "*sqlite*"
> Desired=Unknown/Install/Remove/Purge/Hold
> | Status=Not/Inst/Conf-files/Unpacked/halF-conf/Half-inst/trig-aWait/Trig-pend
> |/ Err?=(none)/Reinst-required (Status,Err: uppercase=bad)
> ||/ Name Version Architecture Description
> +++-=======================-==============-============-=================================
> ii libqt5sql5-sqlite:amd64 5.11.3+dfsg1-4 amd64 Qt 5 SQLite 3
> database driver
> ii libsqlite3-0:amd64 3.29.0-2 amd64 SQLite 3 shared
> library
> ii sqlitebrowser 3.11.2-1 amd64 GUI editor for SQLite
> databases
--
Best regards
dirdi
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
