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:
Specify an INT type on the t.dict column.
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;
----^