On 08.12.2017 12:40, Evgeny Kotkov wrote:
> Bert Huijben <bert_at_qqmail.nl> writes:
>
>>> The recent SQLite versions (starting from 3.8.2, released in December 2013)
>>> feature a `WITHOUT ROWID` optimization [1] that can be enabled when
>>> creating a table. In short, it works well for tables that have non-integer
>>> primary keys, such as
>>>
>>> name TEXT PRIMARY KEY
>>>
>>> by not maintaining the hidden rowid values and an another B-Tree to match
>>> between a primary key value and its rowid. This reduces the on-disk size
>>> and makes the lookups faster (a key → rowid → data lookup is replaced with
>>> a key → data lookup).
>> It doesn't add another B-tree for the primary key and its rowids. For the
>> primary key the main table is used as the index.
>>
>> The case where things differ is when there are multiple indexes. In this
>> case normal table will always refer to the primary key using the rowed,
>> while for 'WITHOUT ROWID' there will be referred to the primary key,
>> which in general is larger.
> For the sake of finding out the truth :), I think that this contradicts the
> explanation in https://sqlite.org/withoutrowid.html :
>
> CREATE TABLE IF NOT EXISTS wordcount(
> word TEXT PRIMARY KEY,
> cnt INTEGER
> );
>
> As an ordinary SQLite table, "wordcount" is implemented as two separate
> B-Trees. The main table uses the hidden rowid value as the key and stores
> the "word" and "cnt" columns as data. The "TEXT PRIMARY KEY" phrase of
> the CREATE TABLE statement causes the creation of an unique index on
> the "word" column. This index is a separate B-Tree that uses "word" and
> the "rowid" as the key and stores no data at all. Note that the
> complete text of every "word" is stored twice: once in the main
> table and again in the index.
>
> Although I didn't check if the most recent SQLite version still behaves in
> the described way internally, I have witnessed the described close-to-2x
> reduction in the size of rep-cache.db — which, unless I am missing
> something, follows the described idea of this optimization.
I suspect most of the performance improvement is a consequence of the
reduced database size, since it needs less page-in operations for the
same number of lookups. The B-tree search should really be trivial
compared to uncached I/O.
-- Brane
Received on 2017-12-08 12:46:48 CET