Re: [sqlite] Warning automatic index on

[Richard Hipp]

On 10/24/16, Werner Kleiner  wrote:
>
> In an error log there is a message like:
> SQlite warning (284) automatic index on is_mytable(internalvalue)
>

I do not know why SQLite is warning you about a lack of an index on
internalvalue when it is really missing an index on installid.  I am
unable to reproduce the problem.  If you can send us a script that
will generate the incorrect warning message, we'll happily look into
the problem.

-- 
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] Warning automatic index on

[Werner Kleiner]

The result is:

2015-05-04 19:13:25 850c11866686a7b39d7b163fb60898c11283688e

2016-10-24 14:28 GMT+02:00 Richard Hipp :
> Please post the result of the following query:
>
> SELECT sqlite_source_id();
>
> On 10/24/16, Werner Kleiner  wrote:
>> I have dropped the double index
>> DROP INDEX 'InternalName';
>> Then executed the SQL Select statement which caused the sqlite warning.
>> But same warning, nothing changed.
>>
>> Then I added a new index CREATE INDEX idx_installid ON ...
>>
>>
>> Now the sqlite warning is gone away. :-)
>>
>> The SQL statement has a JOIN like;
>> ..
>> LEFT JOIN
>> is_mytable H ON H.InstallD = I.InstallD
>>
>> perhaps this was the problem?
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
> --
> 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
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Warning automatic index on

[Richard Hipp]

Please post the result of the following query:

SELECT sqlite_source_id();

On 10/24/16, Werner Kleiner  wrote:
> I have dropped the double index
> DROP INDEX 'InternalName';
> Then executed the SQL Select statement which caused the sqlite warning.
> But same warning, nothing changed.
>
> Then I added a new index CREATE INDEX idx_installid ON ...
>
>
> Now the sqlite warning is gone away. :-)
>
> The SQL statement has a JOIN like;
> ..
> LEFT JOIN
> is_mytable H ON H.InstallD = I.InstallD
>
> perhaps this was the problem?
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


-- 
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] Warning automatic index on

[Werner Kleiner]

I have dropped the double index
DROP INDEX 'InternalName';
Then executed the SQL Select statement which caused the sqlite warning.
But same warning, nothing changed.

Then I added a new index CREATE INDEX idx_installid ON ...


Now the sqlite warning is gone away. :-)

The SQL statement has a JOIN like;
..
LEFT JOIN
is_mytable H ON H.InstallD = I.InstallD

perhaps this was the problem?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Warning automatic index on

[Richard Hipp]

On 10/24/16, Simon Slavin  wrote:
>
> I think it's the "DESC".  Since SQLite understands how to search an index
> backwards when it needs to, try dropping the DESC on that index and see if
> the problem goes away.

He already has another index without the DESC :-\

I think those indexes are redundant.  I cannot, off hand, think of a
set of queries that would benefit from having them both.  SQLite is
perfectly capable using a DESC index for an ASC scan and vice verse.

I don't think the MySQL-backtics are a factor either.  Though they
should be fixed, SQLite at least is able to deal with them.
-- 
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] Warning automatic index on

[Simon Slavin]

On 24 Oct 2016, at 10:11am, Rowan Worth  wrote:

> However I see you already have an index 'InternalName' which covers the
> 'internalvalue' column, so not sure why that isn't being used. Maybe it's
> confused by the DESC or the MySQL back-ticks?

I think it's the "DESC".  Since SQLite understands how to search an index 
backwards when it needs to, try dropping the DESC on that index and see if the 
problem goes away.

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

Re: [sqlite] Warning automatic index on

[Rowan Worth]

On 24 October 2016 at 16:59, Werner Kleiner  wrote:

> Hello,
>
> In an error log there is a message like:
> SQlite warning (284) automatic index on is_mytable(internalvalue)
>
> What does this mean?
>

It means that SQLite's query optimiser has decided the most efficient way
to get the results you asked for is to:

(a) create an index on the 'internalvalue' column of 'is_mytable'
(b) use the index from (a) to help execute the query
(c) drop the index created in (a)

However I see you already have an index 'InternalName' which covers the
'internalvalue' column, so not sure why that isn't being used. Maybe it's
confused by the DESC or the MySQL back-ticks?


What query are you running which produces the warning?

What version of SQLite are you using?
-Rowan
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users