On 11/8/17, korablev <kitn...@gmail.com> wrote:
> sqlite> create table t1(a primary key, b) without rowid;
> sqlite> create table t2(a primary key, b) without rowid;
> sqlite> explain query plan select * from t1, t2 where t1.b = t2.b;
> 0|0|0|SCAN TABLE t1
> 0|1|1|SCAN TABLE t2
> sqlite> create table t3(a primary key, b);
> sqlite> create table t4(a primary key, b);
> sqlite> explain query plan select * from t4, t3 where t3.b = t4.b;
> 0|0|0|SCAN TABLE t4
> 0|1|1|SEARCH TABLE t3 USING AUTOMATIC COVERING INDEX (b=?)
>
> Even if I inserted ~1000000 rows in t3 and t4, query planner anyways
> wouldn't use automatic indexes. So, why tables without rowid can't use
> automatic indexes optimization?

Correct.  Automatic indexes do not work with WITHOUT ROWID tables. I
had forgotten about that limitation. Adding support for automatic
indexes on WITHOUT ROWID tables is a reasonable feature request.

The technical reason for this is that, from the point of view of the
query planner, a WITHOUT ROWID table is really a covering index, and
the query planner never tries to create an index on an index.  We'll
need to go in and teach the query planner that a WITHOUT ROWID table
is a special case.


-- 
D. Richard Hipp
d...@sqlite.org
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Reply via email to