Running just "explain some query" will give you the virtual machine program that it plans on using. You can then scan through that to see what it's doing. Note that the descriptions on the below page for those op codes are sometimes really confusing and it can take a while to decypher what's going on.
https://www.sqlite.org/opcode.html "(Assumption) Any row in the relation table that does NOT meet the WHERE clause is ignored." Down below, lines 5 and 6 (and 24) are in the outer loop and basically say "if waStatsIDCount is not null then go to the next record", so correct. "For each qualifying row in the relation table read from waSTATSINFO_VT using the PK index to try and find a match" Line 14 shows that it's using waStatsInfo_VT (the main rowid/integer primary key table) for that part of the program. and Line 17 has it trying to find a matching rowid "Where there is a match, update the relation table." Even if there isn't a match. In that case it would get updated with null. All of the "whether or not to actually do the update" bits have already been looked at. SQLite version 3.20.1 2017-08-24 16:21:36 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> create table relation (relationKey integer primary key, waStatsIDCount, queryID); sqlite> create table waStatsInfo_VT (relationKey integer primary key, waStatsIDCount); sqlite> explain query plan update relation set waStatsIDCount = (select src.waStatsIDCount from waStatsInfo_VT as src where src.relationKey = relation.relationKey) where waStatsIDCount is null and queryID = 2; selectid|order|from|detail 0|0|0|SCAN TABLE relation 0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 0 0|0|0|SEARCH TABLE waStatsInfo_VT AS src USING INTEGER PRIMARY KEY (rowid=?) sqlite> explain update relation set waStatsIDCount = (select src.waStatsIDCount from waStatsInfo_VT as src where src.relationKey = relation.relationKey) where waStatsIDCount is null and queryID = 2; addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Init 0 26 0 00 Start at 26 1 Null 0 1 2 00 r[1..2]=NULL 2 OpenWrite 0 2 0 3 00 root=2 iDb=0; relation 3 Explain 0 0 0 SCAN TABLE relation 00 4 Rewind 0 25 0 00 5 Column 0 1 6 00 r[6]=relation.waStatsIDCount 6 NotNull 6 24 0 00 if r[6]!=NULL goto 24 7 Column 0 2 7 00 r[7]=relation.queryID 8 Ne 8 24 7 (BINARY) 51 if r[7]!=r[8] goto 24 9 Rowid 0 2 0 00 r[2]=rowid 10 IsNull 2 25 0 00 if r[2]==NULL goto 25 11 Null 0 3 0 00 r[3]=NULL 12 Null 0 9 9 00 r[9..9]=NULL; Init subquery result 13 Integer 1 10 0 00 r[10]=1; LIMIT counter 14 OpenRead 1 3 0 2 00 root=3 iDb=0; waStatsInfo_VT 15 Explain 0 0 0 SEARCH TABLE waStatsInfo_VT AS src USING INTEGER PRIMARY KEY (rowid=?) 00 16 Rowid 0 11 0 00 r[11]=rowid 17 SeekRowid 1 20 11 00 intkey=r[11]; pk 18 Column 1 1 9 00 r[9]=waStatsInfo_VT.waStatsIDCount 19 DecrJumpZero 10 20 0 00 if (--r[10])==0 goto 20 20 SCopy 9 4 0 00 r[4]=r[9] 21 Column 0 2 5 00 r[5]=relation.queryID 22 MakeRecord 3 3 7 D 00 r[7]=mkrec(r[3..5]) 23 Insert 0 7 2 relation 07 intkey=r[2] data=r[7] 24 Next 0 5 0 01 25 Halt 0 0 0 00 26 Transaction 0 1 2 0 01 usesStmtJournal=0 27 Integer 2 8 0 00 r[8]=2 28 Goto 0 1 0 00 sqlite> -----Original Message----- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of David Wellman Sent: Friday, August 24, 2018 7:47 AM To: SQLite Users Subject: [sqlite] Query optimisation HI all, I would like to use the following example as a learning exercise for myself to check my understanding of part of sqlite processing. I have the following query which functionally works fine, and to be upfront about it the volume of data is so small that performance is not an issue. The query: update relation set wastatsidcount = (select src.wastatsidcount from waSTATSINFO_VT as src where src.relationkey = relation.relationkey) where wastatsidcount is null and queryid = 2; The plan: If I run 'explain query plan' on this command it gives the following: SelectedID Order From Detail 0 0 0 SCAN TABLE relation 0 0 0 EXECUTE CORRELATED SCALAR SUBQUERY 0 0 0 0 SEARCH TABLE waSTATSINFO_VT AS src USING INTEGER PRIMARY KEY (rowid=?) My 'src' table is defined with a PRIMARY KEY on column RELATIONKEY. The same column is also the primary key on the 'relation' table. My understanding of this plan is: - Read the relation table using a full table scan - (Assumption) Any row in the relation table that does NOT meet the WHERE clause is ignored. - For each qualifying row in the relation table read from waSTATSINFO_VT using the PK index to try and find a match - Where there is a match, update the relation table. Questions: - Is my assumption above ("Any row in the relation table that does NOT meet the WHERE clause is ignored") correct? - Is there any form of 'explain' or other diagnostic output which would show me this? I tried the 'explain query plan' with and without the full WHERE clause and it didn't change the output (I didn't understand the output from the plain 'explain' command!) In this particular example I need the WHERE clause as coded in order to give me the correct answer, but as I said at the start I'm trying to deepen my knowledge of SQLite performance and it's optimiser. I'm currently using v3.20.1. Cheers, Dave Ward Analytics Ltd - information in motion Tel: +44 (0) 118 9740191 Fax: +44 (0) 118 9740192 www: http://www.ward-analytics.com Registered office address: The Oriel, Sydenham Road, Guildford, Surrey, United Kingdom, GU1 3SR Registered company number: 3917021 Registered in England and Wales. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
