Re: [sqlite] without rowid and secondary indexes

> Le 10 avr. 2018 à 18:25, Clemens Ladisch a écrit : > > Olivier Mascia wrote: >> Would you mean: >> >> select NAME,count(NAME) from SHEETS where SHEET>? group by NAME having NAME >> is not NULL; >> >> is more logically appropriate? > > Probably. The first query can

Re: [sqlite] without rowid and secondary indexes

Olivier Mascia wrote: >> Le 10 avr. 2018 à 13:20, Clemens Ladisch a écrit : >>> select NAME,count(NAME) from SHEETS group by NAME having SHEET>? and NAME >>> is not NULL; >> >> The SHEET value is from some random row in the group, and the NAME value is >> the same for all

Re: [sqlite] without rowid and secondary indexes

> Le 10 avr. 2018 à 13:20, Clemens Ladisch a écrit : > >> select NAME,count(NAME) from SHEETS group by NAME having SHEET>? and NAME is >> not NULL; > > The SHEET value is from some random row in the group, and the NAME value is > the same for all rows in the group. Are you

Re: [sqlite] without rowid and secondary indexes

Olivier Mascia wrote: > [...] > So I should probably even better go with: > > create index ... where NAME is not NULL; > > as my only queries involving NAME have a 'where NAME is not NULL' restriction. To be sure, check with EXPLAIN QUERY PLAN whether the index is actually used. > select

Re: [sqlite] without rowid and secondary indexes

Thanks! > 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. Then, I will indeed run some more tests, but

Re: [sqlite] without rowid and secondary indexes

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