Automatic index, despite existing index?

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

Automatic index, despite existing index?

Olivier Mascia
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?

--
Best Regards, Meilleures salutations, Met vriendelijke groeten,
Olivier Mascia, http://integral.software


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Automatic index, despite existing index?

Clemens Ladisch
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Automatic index, despite existing index?

R Smith
In reply to this post by Olivier Mascia
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Automatic index, despite existing index?

Simon Slavin-3
In reply to this post by Olivier Mascia


On 1 Dec 2017, at 1:50pm, Olivier Mascia <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Automatic index, despite existing index?

Olivier Mascia
In reply to this post by Clemens Ladisch
> Le 1 déc. 2017 à 15:00, Clemens Ladisch <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users