On 3/6/12 5:35 PM, "Philip Martin" <philip.martin_at_wandisco.com> wrote:
>Philip Martin <philip.martin_at_wandisco.com> writes:
>
>> It may be TEXT but it is also PRIMARY KEY and according to the SQLite
>> docs:
>>
>> http://sqlite.org/lang_createtable.html
>>
>> INTEGER PRIMARY KEY columns aside, both UNIQUE and PRIMARY KEY
>> constraints are implemented by creating an index in the database (in
>> the same way as a "CREATE UNIQUE INDEX" statement would). Such an
>> index is used like any other index in the database to optimize
>> queries. As a result, there often no advantage (but significant
>> overhead) in creating an index on a set of columns that are already
>> collectively subject to a UNIQUE or PRIMARY KEY constraint.
>
>If I create a repository using 1.7 and look at the rep-cache.db I see:
>
>$ sqlite3 rep-cache.db "select * from sqlite_master" | grep index
>index|sqlite_autoindex_rep_cache_1|rep_cache|4|
>
>An index has been created automatically and so adding another index can
>only slow things down.
Yeah this is interesting; you've provided a wealth of information contrary
to everything I said to Daniel yesterday. I did some SQLite index tuning
a month ago and I could have sworn having a TEXT field as primary key
inhibited index creation -- but, as you've demonstrated with your explain
plan and sqlite_master query, that's clearly not the case.
To rewind things a little: I was manually repairing my asf mirror
yesterday that happened to sync earlier last week at the wrong time and
picked up a dodgy revision. I manually deleted the affected rows from
rep-cache.db and noticed that all my select queries seemed to be taking an
inordinate amount of time to complete (5s+ at least). I explain plan the
problematic query, saw no indexes, created one manually, and wallah,
problem solved, all queries returned instantly and explain plan showed
index usage.
I've got a bunch of zfs snapshots of the repo I can have a play around
with tomorrow to see if I can replicate.
Plausible theories off the top of my head:
a) There's something different with my env and indexes were not being
created automatically. (I do remember having a lot of trouble getting the
asf repo to load from dumps and then complete from subsequent syncs.)
b) There's something else going on.
(Plausible theories? Yes. Good theories? Debatable ;-)
Trent.
Received on 2012-03-06 21:12:52 CET