Table scan and json_tree
(1.1) By skelter on 2021-04-07 22:56:06 edited from 1.0 [source]
I have a variation on the classic "why scan that table?" When the query plan mentions SCAN TABLE jsondumps does it mean a full table scan or just a partial table scan? If full, why? I have an index.
Background: the tables
- jsondumps - two columns, content json and fileSampleId integer
- FileSamplings
- AttrKeys
sqlite> .schema jsondumps
CREATE TABLE jsondumps (
ID INTEGER PRIMARY KEY not null,
fileSampleId INTEGER NOT NULL,
content JSON
);
CREATE INDEX JSONDUMPS_FILESAMPLEID on jsondumps(fileSampleId);
When the query plan mentions SCAN TABLE jsondumps
does it mean a
full table scan or just a partial table scan? If full, why?
I have an index.
sqlite> create temp table newnumvalues as
...> select fileSamplings.ID as fileSamplingsId, AttrKeys.ID as AttrKeysId, json_tree.atom as numval
...> from
...> FileSamplings
...> join
...> jsondumps,json_tree(jsondumps.content)
...> on jsondumps.fileSampleId =FileSamplings.ID
...> join AttrKeys on AttrKeys.name = fullkey
...> where type = 'real' or type = 'integer'
...> and FileSamplings.sampleId=46 ;
QUERY PLAN
|--SCAN TABLE jsondumps
|--SEARCH TABLE FileSamplings USING INTEGER PRIMARY KEY (rowid=?)
|--SCAN TABLE json_tree VIRTUAL TABLE INDEX 1:
`--SEARCH TABLE AttrKeys USING COVERING INDEX ATTR_NAME_IDX (name=?)