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