Automatic indexes don't work on without rowid tables

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

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



--
Sent from: http://sqlite.1065341.n5.nabble.com/
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

Richard Hipp-3
On 11/8/17, korablev <[hidden email]> 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
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 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" <[hidden email] on behalf of [hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

Richard Hipp-3
On 11/8/17, Peter Da Silva <[hidden email]> wrote:
> On 11/8/17, 8:20 AM, "sqlite-users on behalf of Richard Hipp"
> <[hidden email] on behalf of [hidden email]>
> 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
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

korablev
In reply to this post by Richard Hipp-3
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

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