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
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
> ..., -- 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
D. Richard Hipp
sqlite-users mailing list