Could it be that it might need a DESC index?
Could the "automatic index on ..." in the error log be slightly enhanced to give precision on *why* it needs the automatic index?
Is there any way I missed in the API to get a pointer to the statement which is executing while any or all of the messages are emitted?

Re: Automatic index, despite existing index?

Olivier Mascia wrote:
> 20171201 120319.404 284: automatic index on REMINDER(USER_LOGON)
>
> Here is that part of the schema:
>
> CREATE INDEX IX_REMINDER_USER on REMINDER(USER_LOGON);
>
> Could it be that it might need a DESC index?

Re: Automatic index, despite existing index?

My first guess (and I might be wrong) would be that you ORDER BY in some
query on the USER_LOGON or use it with LIKE or such that it needs CASE
INSENSITIVE or non-NULL Indexing or Unique indexing (GROUP BY - though
unlikely that would be require that speciifc autoindex) or such.

If you enhance the Table definition to say:

...
USER_LOGON text NOT NULL COLLATE NOCASE,
...

it might no longer need that Auto-Index - though that may not fit with
your design criteria.

Logging the reason for an autoindex might be complex. What does Explain
Query plan say on that Query that creates the logs? That should make it
clear and is much better info than the logging provides.

Re: Automatic index, despite existing index?

SQLite should not be doing that. It understands that an index can be used "backwards" if it needs to reverse the sort order.

> Could the "automatic index on ..." in the error log be slightly enhanced to give precision on *why* it needs the automatic index?
> Is there any way I missed in the API to get a pointer to the statement which is executing while any or all of the messages are emitted?

The pseudo table names it’s using — tal and mi — suggest that SQLite is creating its own temporary tables in order to execute your functions. If it was indexing real tables of yours it would give the real tablenames.

It does seem that that you need more information than you are getting. What is creating your SQLite log ? Are you using sqlite3_set_authorizer() ?