SQLite Forum

"NOT INDEXED" ineffective with multicolumn WHERE
Login

"NOT INDEXED" ineffective with multicolumn WHERE

(1) By anonymous on 2022-06-21 17:42:52 [source]

With sqlite 3.38.5, I observe that if the WHERE clause contains two column subpredicates, then "NOT INDEXED" has no effect. Given these table schema:

> create table mp (id integer not null, tag integer not null);
> create unique index theindex ON mp (id, tag);

Observed behavior:

sqlite> explain select * from mp where id=5 and tag=9;
addr  opcode         p1    p2    p3    p4             p5  comment      
----  -------------  ----  ----  ----  -------------  --  -------------
0     Init           0     10    0                    0   Start at 10
1     OpenRead       1     3     0     k(2,,)         2   root=3 iDb=0; theindex
…

sqlite> explain select * from mp not indexed where id=5 and tag=9;
addr  opcode         p1    p2    p3    p4             p5  comment      
----  -------------  ----  ----  ----  -------------  --  -------------
0     Init           0     10    0                    0   Start at 10
1     OpenRead       1     3     0     k(2,,)         2   root=3 iDb=0; theindex
…

Expected to see:

That theindex disappears from the explanation when NOT INDEXED is used, as would be the case when a single column is used in the WHERE clause.

> explain select * from mp where id=5;
addr  opcode         p1    p2    p3    p4             p5  comment      
----  -------------  ----  ----  ----  -------------  --  -------------
…
1     OpenRead       1     3     0     k(2,,)         2   root=3 iDb=0; theindex
…
3     SeekGE         1     9     1     1              0   key=r[1]
…

> explain select * from mp not indexed where id=5;
addr  opcode         p1    p2    p3    p4             p5  comment      
----  -------------  ----  ----  ----  -------------  --  -------------
…
1     OpenRead       0     2     0     2              0   root=2 iDb=0; mp
2     Rewind         0     9     0                    0   
3       Column         0     0     1                    0   r[1]=mp.id
…

(2) By Richard Hipp (drh) on 2022-06-21 18:39:56 in reply to 1 [link] [source]

Should be fixed as of check-in bd87d107fe474cee.