Every index is composed of the columns being indexed followed by the
primary key of the table.  In a regular ROWID table, the "primary key"
is the rowid - an integer.  In a WITHOUT ROWID table, the primary key
is whatever you declare the PRIMARY KEY to be.

So whether or not there is more overhead in an index on a WITHOUT
ROWID table versus a normal rowid table depends on your choice of the
PRIMARY KEY.

In your example below, your PRIMARY KEY consists of two integer
instead of just a single rowid integer.  So it will take up slightly
more space.  Not a lot, but some.

Will that make a difference in your application?  I do not know.  I
suggest you try it and see.  This is one of the great beauties of SQL
(any SQL not just SQLite).  You can make a simple schema change like
this and try it out, without having to modify your application -
indeed without having to even recompile your application.  Simply run
the same build of your application using two different versions of the
database - one with an ordinary rowid table and the other with a
WITHOUT ROWID table - and see which one gives you better performance -
for whatever definition of "performance" is important to you.

On 4/10/18, Olivier Mascia <o...@integral.be> wrote:
> Dear all,
>
> With https://www.sqlite.org/withoutrowid.html in mind, assuming something
> simple as:
>
>   create table if not exists SHEETS(
>   NODE integer not null,
>   SHEET integer not null,
>   NAME text,               -- often NULL, sometimes short text value
> (keyword)
>   ...,                     -- 2 or 3 other small properties
>   primary key(NODE,SHEET)
>   ) without rowid;
>   -- makes sense because small row and natural primary key is not integer
>
> Now what if an index on NAME becomes valuable?
> Will such a secondary index have a higher storage cost or performance cost
> than with an ordinary table?
>
>   create index if not exists SHEETS_NAME on SHEETS(NAME);
>
> --
> Best Regards, Meilleures salutations, Met vriendelijke groeten,
> Olivier Mascia
>
>
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


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