Re: [sqlite] What's the purpose of the "automatic index on" warning message?
On 8 Apr 2014, at 2:22pm, Jens Miltnerwrote: > 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?
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?
On Tue, 08 Apr 2014 15:22:18 +0200 Jens Miltnerwrote: > 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?
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?
On Mon, Apr 7, 2014 at 11:51 AM, Jens Miltnerwrote: > 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