Am 07.04.2014 um 18:42 schrieb Richard Hipp <d...@sqlite.org>:
> On Mon, Apr 7, 2014 at 11:51 AM, Jens Miltner <j...@mac.com> 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
