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
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
