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.
On 2017/12/01 3:50 PM, Olivier Mascia wrote:
Hi all,
I'm also seeing such things in my SQLite log:
20171201 120319.404 284: automatic index on REMINDER(USER_LOGON)
20171201 120319.404 284: automatic index on REMINDER(USER_LOGON)
20171201 120326.763 284: automatic index on REMINDER(USER_LOGON)
20171201 120326.763 284: automatic index on REMINDER(USER_LOGON)
20171201 120330.887 284: automatic index on REMINDER(USER_LOGON)
20171201 120330.887 284: automatic index on REMINDER(USER_LOGON)
Here REMINDER is actually a table and USER_LOGON a colomn).
Here is that part of the schema:
---
sqlite> .schema REMINDER
CREATE TABLE REMINDER(PKID integer primary key not null,
CONTENT text,
USER_LOGON text,
ALARM boolean default 0,
BELL_TS timestamp default (now()),
FK_EVT integer,
FK_MSG integer,
FK_FAX integer,
FOLDER_ID integer,
FK_TIK integer,
SENT_TS timestamp,
foreign key(FK_EVT) references EVT(PKID) on update cascade on delete
cascade,
foreign key(FK_FAX) references EDOCUMENTS(PKID) on update cascade on
delete cascade,
foreign key(FK_MSG) references MSG(PKID) on update cascade on delete
cascade);
CREATE INDEX IFK_REMINDER_EVT on REMINDER(FK_EVT);
CREATE INDEX IFK_REMINDER_DOC on REMINDER(FK_FAX);
CREATE INDEX IFK_REMINDER_MSG on REMINDER(FK_MSG);
CREATE INDEX IX_REMINDER_USER on REMINDER(USER_LOGON);
CREATE TRIGGER TR_REMINDERINSERT after insert on REMINDER begin select
signal_reminder();end;
CREATE TRIGGER TR_REMINDERDELETE after delete on REMINDER begin select
signal_reminder();end;
---
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?
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
