Usage of indexes and subqueries

(1) By cguerber on 2020-05-08 10:50:27

Hi all,

Here is a simple scenario:

`CREATE TABLE dict(field1);
CREATE TABLE t(field1,dict1 REFERENCES dict(rowid),dict2, UNIQUE(dict1,dict2));`

Now:

`EXPLAIN QUERY PLAN UPDATE t SET field1=1 WHERE dict1=1 AND dict2=2;
0|0|0|SEARCH TABLE t USING INDEX sqlite_autoindex_t_1 (dict1=? AND dict2=?)`

But:

`EXPLAIN QUERY PLAN UPDATE t SET field1=1 WHERE dict1=(SELECT rowid FROM dict WHERE field1='z') AND dict2=2;
0|0|0|SCAN TABLE t
0|0|0|EXECUTE SCALAR SUBQUERY 0
0|0|0|SCAN TABLE dict`

The first explain shows that it uses the autoindex on t. But the second shows that the autoindex on t is not used anymore.

I'm using SQLite version 3.16.2 2017-01-06 16:32:41 on a Debian Linux.

Questions: Why is it so? Do newer version perform better (by not forgetting to use the index)? Is there a way to rewrite the second UPDATE so that is uses the index?

(2) By Richard Hipp (drh) on 2020-05-08 11:52:01 in reply to 1 [link]

This is a problem with [type affinity][1].

[1]: https://www.sqlite.org/datatype3.html#type_affinity

The constant 1 has a type affinity of 'none'.
The subquery '(SELECT rowid FROM ...)' has a type affinity of 'integer'.
The t.dict1 has a type affinity of 'blob'.

You can drive an index that has type affinity 'blob' with a value that
has a type affinity of 'none', but not one that has a type affinity
of 'integer'.  The reason you cannot drive a blob index with an integer
value is that the index (or column t.dict1 in this case) might contain
a string value that looks like a integer:  '1' instead of 1.  But there
is no way to check for that efficiently using the index.

Two possible solutions:

  1.  Specify an INT type on the t.dict column.

  2.  Add a "+" sign before the "rowid" on the subquery, or before the
      subquery itself, to force the expression to use affinity 'none'.

So either:

~~~~~
CREATE TABLE dict(field1);
CREATE TABLE t(field1,dict1 INT REFERENCES dict(rowid),dict2, UNIQUE(dict1,dict2));
                    --------^^^
EXPLAIN QUERY PLAN
UPDATE t SET field1=1 WHERE dict1=(SELECT rowid FROM dict WHERE field1='z') AND dict2=2;
~~~~~

Or this:

~~~~~
CREATE TABLE dict(field1);
CREATE TABLE t(field1,dict1 INT REFERENCES dict(rowid),dict2, UNIQUE(dict1,dict2));
EXPLAIN QUERY PLAN
UPDATE t SET field1=1 WHERE dict1=(SELECT +rowid FROM dict WHERE field1='z') AND dict2=2;
                                      ----^
~~~~~
Or this:

~~~~~
CREATE TABLE dict(field1);
CREATE TABLE t(field1,dict1 INT REFERENCES dict(rowid),dict2, UNIQUE(dict1,dict2));
EXPLAIN QUERY PLAN
UPDATE t SET field1=1 WHERE dict1=+(SELECT rowid FROM dict WHERE field1='z') AND dict2=2;
                              ----^
~~~~~