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