> -----Original Message-----
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of Richard Hipp
> Sent: maandag 2 september 2013 16:11
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Inefficient covering index used for Subversion with
> SQLite 3.8.0
>
> On Mon, Sep 2, 2013 at 9:42 AM, Bert Huijben <rhuij...@apache.org>
> wrote:
>
> >
> > My question is more like: Should I see this as a light regression that
most
> > likely will be resolved in a future version, or as a buggy assumption in
> > our
> > code that I should work around? It should be possible via explicit
'INDEXED
> > BY' clauses and/or breaking the query in separate parts. (Or for future
> > versions by pre-filling the stat table)
> >
>
> I think rules #3 and #4 (especially #4) of
> http://www.sqlite.org/queryplanner-ng.html#howtofix are applicable here.
>
> Without the results of ANALYZE, SQLite assumes that the left-most column
of
> an index will narrow down a search to about 10 rows. That is clearly not
> the case with your schema (where the left-most column of several indices,
> wc_id, is always the same value). Hence, SQLite really needs the results
> of ANALYZE to work efficiently.
>
> But that does not mean you have to run ANALYZE on each installation.
> Instead, you can install a pre-computed sqlite3_stat1 table (the larger of
> the two you emailed above would suffice) whenever you create a new
> database. To do this, just run:
>
> ANALYZE sqlite_master; -- Create the sqlite_stat1 table
> INSERT INTO sqlite_stat1 VALUES(...); -- repeat as necessary to fill
> in the table.
> ANALYZE sqlite_master; -- Load the sqlite_stat1 table into the
> optimizer.
>
> The above steps only need to be done once, when the database is first
> created, and can be part of the same script that creates all the other
> tables, indices, triggers, and views in your database. You should never
> need to run ANALYZE again (assuming the "shape" of your data is always
> roughly the same). The sqlite_stat1 table created here gives SQLite all
> the information it needs to be able to figure out the best way to handle
> queries in your peculiar usage pattern.
Thanks.
We anticipate that the wc_id column will be used more in future versions and
I had hoped that the assumption that a better index match (matching more
usable columns) would always be preferable over one that uses less columns.
All indexes have the wc_id column first, so I don't see a good reason that
Sqlite in this case prefers only this column of the specific (bad) index
over any other index that can help with the rest of the query.
This optimization added in 3.7.12 made it easier for us to write clearer
well performing queries, without any hassle. . . We can't really assume our
users to run a specific version of Sqlite (as most unix distributions
compile sqlite themselves for products like ours)...
Most Subversion developers are not database experts, so adding this
initialization will add more code that will have to be explained over and
over again to developers that don't understand how a query optimizer (or a
B-Tree) works.
Thanks for the call ANALYZE twice trick. That avoids having to declare the
table ourselves.
Bert
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
