Re: [sqlite] What's the purpose of the "automatic index on" warning message?

[Simon Slavin]

On 8 Apr 2014, at 2:22pm, Jens Miltner  wrote:

> So what would cause SQLite not being able to use one of the two indexes I 
> have?

First, run "ANALYZE".

Then run "EXPLAIN QUERY PLAN ".

This may give you some clues about how SQLite is understanding your SELECT 
requirements when it works out a search strategy.

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

Re: [sqlite] What's the purpose of the "automatic index on" warning message?

[Clemens Ladisch]

Jens Miltner wrote:
> apart from a JOIN statement, there is no WHERE clause relating to table "a"

For purposes of optimization, an inner join is the same as a WHERE clause.

> LEFT JOIN a ON a.b_id=b.id AND a.identifier=x.identifier

An outer join, however, requires that the left table is used for the
outer loop of the nested loop join, i.e., the database must take each
record in a and looks up the corresponding record(s) in b.  An index
for a helps only when there is some (other) WHERE clause that restricts
the eligible records in a.


In theory, an index on a (automatic or not) should not be necessary for
this query.  In practice, what is the output of EXPLAIN QUERY PLAN for
this query, and what is the column reported by the warning?


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] What's the purpose of the "automatic index on" warning message?

[Eduardo Morras]

On Tue, 08 Apr 2014 15:22:18 +0200
Jens Miltner  wrote:

> CREATE INDEX a_idx1 ON a(b_id);
> CREATE INDEX a_idx2 ON a(identifier, b_id);
> 
> both of which could be used according to the JOIN statement and/or
> the CASE statement (if this part would use an index at all).
> 
> 
> I understand it's hard to tell where the problem is without knowing
> the complete query details, but maybe some hint on what would prevent
> the use of a regular index might help pointing me in the right
> direction...

Did you run ANALYZE?

Is b_id table a primary key? If it is then delete it because Sqlite adds 
primary key on indexs by default. Run analyze/reindex after change.

> 
> Thanks,
> -jens
> 
> 


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

Re: [sqlite] What's the purpose of the "automatic index on" warning message?

[Jens Miltner]

Am 07.04.2014 um 18:42 schrieb Richard Hipp :

> On Mon, Apr 7, 2014 at 11:51 AM, Jens Miltner  wrote:
> 
>> We get an sqlite3_log() message with errorCode 284 and message "automatic
>> index on ...".
>> I assume this is some performance penalty warning, but I have no idea what
>> to make of it:
>> 
>> We do have an explicit index on the table and column mentioned in the
>> warning message, so I don't know what to do to avoid this warning and
>> potentially improve the query performance.
>> 
> 
> The warning is to let you know that SQLite could not find a way to use your
> index and so it had to make its own index, which might result in a query
> that is slower than you were counting on.

So what would cause SQLite not being able to use one of the two indexes I have?
Unfortunately, the actual query itself is somewhat complex and requires 
temporary tables & views, so I can't easily post it with enough context, but 
apart from a JOIN statement, there is no WHERE clause relating to table "a" 
(which is the one for which the auto index message is logged). The only 
additional place in the query where table "a" is referenced is in a CASE 
statement in the SELECT part, like

SELECT DISTINCT
...
CASE WHEN a.identifier NOT NULL THEN CASE IFNULL(c.state, 0) IN (1, 2, 
4) THEN 0 ELSE 1 END ELSE 1 END AS aState,
...
FROM
...
LEFT JOIN a ON a.b_id=b.id AND a.identifier=x.identifier
...

These are the only references to table "a" in the query and we do have a couple 
of indexes on a, the two that would apply here are

CREATE INDEX a_idx1 ON a(b_id);
CREATE INDEX a_idx2 ON a(identifier, b_id);

both of which could be used according to the JOIN statement and/or the CASE 
statement (if this part would use an index at all).


I understand it's hard to tell where the problem is without knowing the 
complete query details, but maybe some hint on what would prevent the use of a 
regular index might help pointing me in the right direction...

Thanks,
-jens


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

Re: [sqlite] What's the purpose of the "automatic index on" warning message?

[Richard Hipp]

On Mon, Apr 7, 2014 at 11:51 AM, Jens Miltner  wrote:

> We get an sqlite3_log() message with errorCode 284 and message "automatic
> index on ...".
> I assume this is some performance penalty warning, but I have no idea what
> to make of it:
>
> We do have an explicit index on the table and column mentioned in the
> warning message, so I don't know what to do to avoid this warning and
> potentially improve the query performance.
>

The warning is to let you know that SQLite could not find a way to use your
index and so it had to make its own index, which might result in a query
that is slower than you were counting on.


>
> Can anybody shed light on this warning message (it's issued by the code in
> sqlite3.c, line 11008)?
>
>
> Thanks,
> -jens
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>


-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users