On 11/8/17, korablev <[email protected]> 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 [email protected] _______________________________________________ sqlite-users mailing list [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
