SQLite Forum

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?