Re: [sqlite] Automatic index, despite existing index?
> Le 1 déc. 2017 à 15:00, Clemens Ladisch a écrit : > > 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? > > Probably not; the index direction usually does not matter. > > But are you using COLLATE NOCASE (or LIKE)? COLLATE is used nowhere, but sure, LIKE is liked by some people here. I'll have to check the code closer. If I'm following you, if there are lookups on REMINDER(USER_LOGON) using LIKE it somehow implies some COLLATE NOCASE and so would like such an index? If so, there is an opportunity for enhancement in the logging. Something like: automatic index on REMINDER(USER_LOGON collate nocase) -- Best Regards, Meilleures salutations, Met vriendelijke groeten, Olivier Mascia, http://integral.software ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Automatic index, despite existing index?
On 1 Dec 2017, at 1:50pm, Olivier Mascia wrote: > Could it be that it might need a DESC 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() ? Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] 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. 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
Re: [sqlite] 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? Probably not; the index direction usually does not matter. But are you using COLLATE NOCASE (or LIKE)? Regards, Clemens ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
