Consider the following SQL:
<blockquote><pre>
CREATE TABLE t1(a INTEGER PRIMARY KEY, b);
CREATE TABLE t2(x INTEGER PRIMARY KEY, y);
SELECT b FROM t1
WHERE t1.a IN (SELECT x FROM t2 WHERE y=?);
</pre></blockquote>
The query planner is creating an automatic index for the T2.Y column even
though a simple linear scan of T2 would be a less costly plan.
This problem was introduced in version 3.7.0, which is the first version
of SQLite to support automatic indices. The work-around is to use
<blockquote><pre>
PRAGMA automatic_index=OFF;
</pre></blockquote>
to disable the automatic index mechanism, or to compile SQLite using
the -DSQLITE_OMIT_AUTOMATIC_INDEX compile-time option.
|