> By default, each SQLite table row has a unique rowId, which works like a primary key if one isn’t explicitly defined.
It actually uses rowid even if you have a primary key.
You should try visualizing the primary key index for a WITHOUT ROWID table. Those indexes are my favourite
> Both Indexes look similar, but the second Index, with fewer Pages, should be faster.
Less nodes doesn’t really mean “faster”. The most important is the height of the tree.
The second most important is what happens when you find your value in the index. Do you need to load the rest from a separate table(rowid)? Or is the data just there for you (without rowid)? Especially range queries (aka where 50<= col <=100)
> By default, each SQLite table row has a unique rowId, which works like a primary key if one isn’t explicitly defined.
It actually uses rowid even if you have a primary key.
You should try visualizing the primary key index for a WITHOUT ROWID table. Those indexes are my favourite
> Both Indexes look similar, but the second Index, with fewer Pages, should be faster.
Less nodes doesn’t really mean “faster”. The most important is the height of the tree.
The second most important is what happens when you find your value in the index. Do you need to load the rest from a separate table(rowid)? Or is the data just there for you (without rowid)? Especially range queries (aka where 50<= col <=100)