[svn.haxx.se] · SVN Dev · SVN Users · SVN Org · TSVN Dev · TSVN Users · Subclipse Dev · Subclipse Users · this month's index

Re: [PATCH] Use the `WITHOUT ROWID` SQLite optimization for rep-cache.db

From: Evgeny Kotkov <evgeny.kotkov_at_visualsvn.com>
Date: Fri, 8 Dec 2017 14:40:03 +0300

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
>> 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 :

      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.

Evgeny Kotkov
Received on 2017-12-08 12:40:29 CET

This is an archived mail posted to the Subversion Dev mailing list.