SQLite log - automatic index on sqlite_sq_25FA456860(ID) (284)

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

SQLite log - automatic index on sqlite_sq_25FA456860(ID) (284)

Olivier Mascia
Dear,

On such a log line as:

        automatic index on REMINDER(USER_LOGON) (284)

I understand what it means and what I might want to do (or not).

Though what should I understand from line like these:

        automatic index on sqlite_sq_25FA456860(ID) (284)
        automatic index on sqlite_sq_25FA455E40(ID) (284)
        automatic index on sqlite_sq_25FA456C50(ID) (284)
        automatic index on sqlite_sq_25FA455ED0(ID) (284)
        automatic index on sqlite_sq_25FA4563E0(ID) (284)
        ...

I guess they mean SQLite decided to build some temporary index for some query execution, just as for the first case. But here the table itself looks like internal and temporary. What can I interpret from these?

(I have no apparent problem / issue. Just pinpointed these in the log.)

--
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: SQLite log - automatic index on sqlite_sq_25FA456860(ID) (284)

Richard Hipp-3
On 5/3/17, Olivier Mascia <[hidden email]> wrote:
> automatic index on sqlite_sq_25FA4563E0(ID) (284)
> ...
>
> I guess they mean SQLite decided to build some temporary index for some
> query execution, just as for the first case. But here the table itself looks
> like internal and temporary. What can I interpret from these?

Yes.  Those sqlite3_sq_... tables are going to be materializations of
views and/or subqueries. The "sq" stands for "subquery".

--
D. Richard Hipp
[hidden email]
_______________________________________________
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: SQLite log - automatic index on sqlite_sq_25FA456860(ID) (284)

Olivier Mascia
> Le 3 mai 2017 à 18:46, Richard Hipp <[hidden email]> a écrit :
>
> On 5/3/17, Olivier Mascia <[hidden email]> wrote:
>> automatic index on sqlite_sq_25FA4563E0(ID) (284)
>> ...
>>
>> I guess they mean SQLite decided to build some temporary index for some
>> query execution, just as for the first case. But here the table itself looks
>> like internal and temporary. What can I interpret from these?
>
> Yes.  Those sqlite3_sq_... tables are going to be materializations of
> views and/or subqueries. The "sq" stands for "subquery".
>
> --
> D. Richard Hipp

Thanks Richard.
What recommended path, if there's one favored, should I take to make it easy to identify which queries induce those indexes on subqueries?

Would there be a cheap way for SQLite to log some more user-realm context about these?
Maybe simply emitting a second call to the log function right after these messages code 284 where the third parameter (msg) would simply point at the prepared statement SQL text (just as sqlite3_sql(sqlite3_stmt*) returns?

--
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: SQLite log - automatic index on sqlite_sq_25FA456860(ID) (284)

Tim Streater-3
On 3 May 2017, at 18:16, Olivier Mascia <[hidden email]> wrote:

> Would there be a cheap way for SQLite to log some more user-realm context
> about these?
> Maybe simply emitting a second call to the log function right after these
> messages code 284 where the third parameter (msg) would simply point at the
> prepared statement SQL text (just as sqlite3_sql(sqlite3_stmt*) returns?

Why don’t you write your own wrapper functions for the base SQLite calls. My set of these functions logs where each call occurs and what the SQL was.


--
Cheers  --  Tim
_______________________________________________
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: SQLite log - automatic index on sqlite_sq_25FA456860(ID) (284)

Olivier Mascia
> Le 3 mai 2017 à 19:44, Tim Streater <[hidden email]> a écrit :
>
>> Would there be a cheap way for SQLite to log some more user-realm context
>> about these?
>> Maybe simply emitting a second call to the log function right after these
>> messages code 284 where the third parameter (msg) would simply point at the
>> prepared statement SQL text (just as sqlite3_sql(sqlite3_stmt*) returns?
>
> Why don’t you write your own wrapper functions for the base SQLite calls. My set of these functions logs where each call occurs and what the SQL was.

That's indeed an obvious path, and our C++ wrapper can easily do so. It indeed gives us precise context information including the sqlexpanded query when appropriate, but only when SQLite raises an error (which is rather exceptional in released code).  Here these are not errors, merely informational messages from SQLite which we can lightly capture on the fly.  Logging all detailed context would be unnecessary bulk. Hence the proposal which is, apparently, lighter and only triggers when SQLite has something useful to say.  I try to keep things simple and light. :)

--
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: SQLite log - automatic index on sqlite_sq_25FA456860(ID) (284)

Rowan Worth-2
In reply to this post by Olivier Mascia
On 4 May 2017 at 01:16, Olivier Mascia <[hidden email]> wrote:

> > Le 3 mai 2017 à 18:46, Richard Hipp <[hidden email]> a écrit :
> >
> > On 5/3/17, Olivier Mascia <[hidden email]> wrote:
> >>      automatic index on sqlite_sq_25FA4563E0(ID) (284)
> >>      ...
> >>
> >> I guess they mean SQLite decided to build some temporary index for some
> >> query execution, just as for the first case. But here the table itself
> looks
> >> like internal and temporary. What can I interpret from these?
> >
> > Yes.  Those sqlite3_sq_... tables are going to be materializations of
> > views and/or subqueries. The "sq" stands for "subquery".
>
> Thanks Richard.
> What recommended path, if there's one favored, should I take to make it
> easy to identify which queries induce those indexes on subqueries?
>

A [conditional] breakpoint in your logging callback might suffice, if you
can reproduce in debug?

In general it might be nice to have some context available from the logging
function; eg. at present it's not even possible to determine which
connection was responsible for the warning.

-Rowan
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users