Thanks! I've send an email with the .fullschema to your private email below. Not sure if there is another better address?
- Deon -----Original Message----- From: drhsql...@gmail.com [mailto:drhsql...@gmail.com] On Behalf Of Richard Hipp Sent: Tuesday, January 16, 2018 6:45 AM To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> Cc: de...@outlook.com Subject: Re: [sqlite] Can I create a stealth index? 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
