On 1/16/18, Deon Brewis <de...@outlook.com> wrote:
>
> I have seen a few cases where a newly added index would start showing up
> uninvited in old, previously tested queries and bring performance down by an
> order of magnitude. ('analyze' doesn't fix it).
We would welcome the opportunity to try to fix such problems. If you
can send us (even by private email) the output of ".fullschema" and
the query in question, identify the offending index, and tell us what
kind of timing discrepancy you are seeing, that would probably be
sufficient to address the problem.
>
> The new indexes would only be needed for new queries, so I don't mind
> forcing an 'indexed by' in for those, but I would like to avoid retrofitting
> every previous query we've ever written with an 'indexed by'.
The following commands will probably prevent 'newindex' from being
used by legacy queries:
DELETE FROM sqlite_stat1 WHERE idx='newidx';
INSERT INTO sqlite_stat1(tbl,idx,stats)
VALUES('thetable','newindex',1000000 1000000 1000000');
In the second line, there should be N+1 copies of '1000000' if there
are N columns in the index. And, of course, 'thetable' must be the
name of the table that 'newindex' is indexing.
You will need to either close and reopen the database connection, or
else run "ANALYZE sqlite_master;" after making the changes above in
order for the changes to go into effect.
--
D. Richard Hipp
d...@sqlite.org
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
