On 3/6/12 5:35 PM, "Philip Martin" <philip.mar...@wandisco.com> wrote:
>Philip Martin <philip.mar...@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.
