On 2019/09/10 2:28 PM, Dominique Devienne wrote:
On Tue, Sep 10, 2019 at 2:09 PM Marek Wieckowski <wiec...@gmail.com> wrote:
Yes, indeed works. Great, thank you!
Note though that it has performance implications perhaps.
This changes to physical structure of the table, to be stored as an index
basically.
So if you do lots of insertions "in the middle", you could have "write
amplifications".
WITHOUT ROWID tables are ideal for read-mostly tables, and function as-if
the
table is an index that covers all columns. So there's no need for separate
IO for the
index (to lookup a PK) then to its table (to lookup the actual row), it's
just one IO.
It's no different from updating an index, except indexes are typically
"skinnier" (have
fewer and smaller columns) than their corresponding tables, limiting the
impact.
I feel like there's some concepts here that are not perfectly defined in
the above statements, or I am mistaken, so please allow me to add:
Any SQLite table is essentially itself a covering Index with mostly
rowid as the indexed key, but in WITHOUT_ROWID tables, it takes whatever
other primary key is offered as the indexed key (not specifying a PK is
an error). There is no other difference, and no difference in
computation and typically a net speed/size gain if your Primary Key is
not exactly INTEGER. (This is the entire point of offering the
WITHOUT_ROWID optimization).
I do not think it is "better" suited to specifically reads or writes -
both can work better (depending on your suitability definition for
"better"). It is however better suited to smaller data widths in the
columns (especially for blobs).
If the above is not true, someone kindly point out to me the truth and
the why of it - thanks!
So "fat" tables, with large rows, and lost of inserts/updates, is basically
the worse case
scenario for such WITHOUT ROWID tables. It works, no issue there, and as
typical of
SQLite is often fast enough for most DB sizes, but it *can* matter. Just be
aware of it.
That is interesting - could you elaborate on how exactly lots of inserts
would be worse in WITHOUT_ROWID tables than in normal tables?*
Or point me to documentation or even a dev explaining it in a forum
post, because I have definitely missed that very important bit of info.
The larger column data thing I do get. I really need to know how it
"can" matter, because I use this setup near everywhere and so far been
blissfully under the impression that it's never worse, and possibly a
little better for non-integer key use-cases.
*[I get that a text key might have slower insertion into a B-Tree than
an integer key might have into a B*-Tree, but if your PK needs to be
text anyway, I don't see how adding it in a normal table (along with the
normal row_id) could have better performance, unless I'm missing
something important.]
My $0.02. --DD
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
