SQLite Forum

Usage of indexes and subqueries
Login

Usage of indexes and subqueries

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

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] [source]

This is a problem with 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;
                              ----^