Philip: I recalled last year's discussions about implied indexes, but
between Trent's reported observations on IRC and a back-of-the-envelope
test with sqlite3(1) I was led to believe that an implied index does not
get created for in this case (due to the TEXT column, as my comment
says).
I'm more than happy to revert this on trunk (if it hasn't been already)
assuming it's indeed superfluous.
Trent -- have you looked into things from your end yet? Can you confirm
or deny the hypothesis that the explicit INDEX was necessary in your
environment?
Trent Nelson wrote on Tue, Mar 06, 2012 at 12:11:57 -0800:
>
>
> 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-14 20:41:34 CET