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