Re: [sqlite] Can I create a stealth index?

[Deon Brewis]

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',100 100 100');

In the second line, there should be N+1 copies of '100' 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

Re: [sqlite] Can I create a stealth index?

[Richard Hipp]

On 1/16/18, Deon Brewis  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',100 100 100');

In the second line, there should be N+1 copies of '100' 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

Re: [sqlite] Can I create a stealth index?

[Dominique Devienne]

On Tue, Jan 16, 2018 at 3:38 PM, Deon Brewis  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).
>

That seems quite surprising. And I'm sure the SQLite devs would like to
know about that. FWIW. --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Can I create a stealth index?

[Deon Brewis]

Can I create an index in SQLITE that is only ever used in an 'indexed by' 
clause and not automatically picked up the query optimizer?

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).

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'.

- Deon

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users