Re: [sqlite] Automatic indexes don't work on without rowid tables

[Richard Hipp]

On 11/8/17, korablev  wrote:
> What is the timeline of this feature? Will it be implemented in the nearest
> future?

Low priority.  I do not like automatic index feature, because I think
that app developers should do an appropriate CREATE INDEX instead.  Of
course, it is not possible to a CREATE INDEX on a subquery, so
automatic indexes make sense in that context, which is why I keep the
optimization around.

But since this has never come up before, it does not seem like a huge
problem and we have a number of other priorities at the moment.

> And what optimizations doesn't also work on without rowid tables? Is
> the query planner good at optimizing stuff on without rowid tables at all?

AFAIK every (except automatic indexes) works for both rowid and
without-rowid tables.  But I had forgotten that automatic indexes
didn't work for without rowid tables, so perhaps there is something
else I have overlooked.

-- 
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

Re: [sqlite] Automatic indexes don't work on without rowid tables

[korablev]

What is the timeline of this feature? Will it be implemented in the nearest
future? And what optimizations doesn't also work on without rowid tables? Is
the query planner good at optimizing stuff on without rowid tables at all?



--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Automatic indexes don't work on without rowid tables

[Richard Hipp]

On 11/8/17, Peter Da Silva  wrote:
> On 11/8/17, 8:20 AM, "sqlite-users on behalf of Richard Hipp"
> 
> wrote:
>> 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
>
> So basically the whole table is stored inline with the primary key index?

That's the basic idea, yes.

-- 
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

Re: [sqlite] Automatic indexes don't work on without rowid tables

[Peter Da Silva]

On 11/8/17, 8:20 AM, "sqlite-users on behalf of Richard Hipp" 
 
wrote:
> 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

So basically the whole table is stored inline with the primary key index?

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Automatic indexes don't work on without rowid tables

[Richard Hipp]

On 11/8/17, korablev  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 ~100 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

[sqlite] Automatic indexes don't work on without rowid tables

[korablev]

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 ~100 rows in t3 and t4, query planner anyways
wouldn't use automatic indexes. So, why tables without rowid can't use
automatic indexes optimization?



--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users