0x11C: automatic index warning for CTEs ?

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

0x11C: automatic index warning for CTEs ?

Howard Kapustein
I'm setting SQLite logging a warning
Warning 0x11C: automatic index on PackageIdByAll(_PackageID)

Whenever I run this SQL

WITH
PackageIdByUser(_PackageID) AS (
    SELECT _PackageID FROM Package AS p
        INNER JOIN PackageUser AS pu ON pu.Package=p._PackageID
        WHERE pu.User=?1 AND p._WorkID=0 AND pu._WorkID=0
),
PackageIdByDefaultAccountForPackageFamilyNotUser(_PackageID) AS (
    SELECT _PackageID FROM Package AS p
        INNER JOIN PackageUser AS pu ON pu.Package=p._PackageID
        WHERE ?4 AND pu.User IN (
            SELECT _UserID FROM User AS u WHERE u.UserSid=?3
        ) AND p.PackageFamily NOT IN (
            SELECT DISTINCT PackageFamily FROM Package AS p
                INNER JOIN PackageUser AS pu ON pu.Package=p._PackageID
                WHERE pu.User=?1 AND p._WorkID=0 AND pu._WorkID=0
        ) AND p._WorkID=0 AND pu._WorkID=0
),
PackageIdByAll(_PackageID) AS (
    SELECT _PackageID FROM PackageIdByUser
    UNION ALL
    SELECT _PackageID FROM PackageIdByDefaultAccountForPackageFamilyNotUser
)
SELECT *
    FROM Protocol AS p
    INNER JOIN ApplicationExtension AS ae ON ae._ApplicationExtensionID=p.Extension
    INNER JOIN Application AS a ON a._ApplicationID=ae.Application
    INNER JOIN Package AS pkg ON pkg._PackageID=a.Package
    INNER JOIN PackageFamily AS pf ON pf._PackageFamilyID=pkg.PackageFamily
    INNER JOIN PackageIdByAll AS cte_p ON cte_p._PackageID=pkg._PackageID
    WHERE p.ProgId=?2 AND p._WorkId=0 AND ae._WorkId=0 AND a._WorkId=0 AND pkg._WorkId=0 AND pf._WorkId=0;

Why?

PackageIdByAll is a CTE. I can't CREATE INDEX for it.

_PackageID is the primary key of the Package table
DROP TABLE IF EXISTS Package;
CREATE TABLE Package(
    _PackageID                    INTEGER PRIMARY KEY /* primarykey           */ NOT NULL,
    ...
So indexing should be covered being the table's ROWID.

I'm using SQLite 3.18.0

Bug or am I holding it wrong?


  *   Howard
_______________________________________________
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: 0x11C: automatic index warning for CTEs ?

Clemens Ladisch
Howard Kapustein wrote:

> I'm setting SQLite logging a warning
> Warning 0x11C: automatic index on PackageIdByAll(_PackageID)
>
> WITH ...
> PackageIdByAll(_PackageID) AS (...)
> SELECT *
>     FROM ...
>     INNER JOIN PackageIdByAll AS cte_p ON cte_p._PackageID=pkg._PackageID
>
> Why?
>
> PackageIdByAll is a CTE. I can't CREATE INDEX for it.

The warning tells you that the database created an automatic index.
This does not imply that you _must_ create an index, it's just a hint
that you should think about whether it is possible to improve the query.
If you have a table, creating the index explicitly is the easiest way,
but for CTEs/views/subqueries, you might have to change the query itself,
or the database schema, or determine that the automatic index is the
best choice in your situation.

> _PackageID is the primary key of the Package table
> So indexing should be covered being the table's ROWID.

When you use the rowid to look up a table row, you still don't know
which CTE row that would correspond to.


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: 0x11C: automatic index warning for CTEs ?

Howard Kapustein
>but for CTEs/views/subqueries, you might have to change the query itself, or the database schema, or determine that the automatic index is the best choice in your situation.

Yes. The question is how?

The CTE's doing a SELECT _PackageID FROM Package where _PackageID is a primary key. If I change the CTE to SELECT rowid FROM Package would SQLite still think an automatic index is needed? Or is the automatic index for the CTE itself? I can only declare the PackageIdByAll CTE has results but no type info e.g.
        WITH PackageIdByAll(_PackageID PRIMARY KEY NOT NULL)
isn't legal.

Right now my log's being spammed with oodles of automatic index messages because of the CTE. I'd like to *fix* this, but right now the only solution I'm seeing is suppressing all automatic index log messages. I'd like to still see REAL ones to not mask new issues.

Suggestions?



-----Original Message-----
From: sqlite-users [mailto:[hidden email]] On Behalf Of Clemens Ladisch
Sent: Friday, April 21, 2017 11:13 PM
To: [hidden email]
Subject: Re: [sqlite] 0x11C: automatic index warning for CTEs ?

Howard Kapustein wrote:

> I'm setting SQLite logging a warning
> Warning 0x11C: automatic index on PackageIdByAll(_PackageID)
>
> WITH ...
> PackageIdByAll(_PackageID) AS (...)
> SELECT *
>     FROM ...
>     INNER JOIN PackageIdByAll AS cte_p ON
> cte_p._PackageID=pkg._PackageID
>
> Why?
>
> PackageIdByAll is a CTE. I can't CREATE INDEX for it.

The warning tells you that the database created an automatic index.
This does not imply that you _must_ create an index, it's just a hint that you should think about whether it is possible to improve the query.
If you have a table, creating the index explicitly is the easiest way, but for CTEs/views/subqueries, you might have to change the query itself, or the database schema, or determine that the automatic index is the best choice in your situation.

> _PackageID is the primary key of the Package table So indexing should
> be covered being the table's ROWID.

When you use the rowid to look up a table row, you still don't know which CTE row that would correspond to.


Regards,
Clemens
_______________________________________________
sqlite-users mailing list
[hidden email]
https://na01.safelinks.protection.outlook.com/?url=http%3A%2F%2Fmailinglists.sqlite.org%2Fcgi-bin%2Fmailman%2Flistinfo%2Fsqlite-users&data=02%7C01%7Choward.kapustein%40microsoft.com%7C97a3efe7e6f7483a93ca08d48946c459%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C636284384420854952&sdata=6DPB0%2Bbtibn2aYYms6AILfHztq7gORwmdEq0uYcsaG0%3D&reserved=0
_______________________________________________
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: 0x11C: automatic index warning for CTEs ?

Keith Medcalf

Change UNION ALL to UNION.

--
˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı

> -----Original Message-----
> From: sqlite-users [mailto:[hidden email]]
> On Behalf Of Howard Kapustein
> Sent: Monday, 24 April, 2017 15:46
> To: SQLite mailing list
> Subject: Re: [sqlite] 0x11C: automatic index warning for CTEs ?
>
> >but for CTEs/views/subqueries, you might have to change the query itself,
> or the database schema, or determine that the automatic index is the best
> choice in your situation.
>
> Yes. The question is how?
>
> The CTE's doing a SELECT _PackageID FROM Package where _PackageID is a
> primary key. If I change the CTE to SELECT rowid FROM Package would SQLite
> still think an automatic index is needed? Or is the automatic index for
> the CTE itself? I can only declare the PackageIdByAll CTE has results but
> no type info e.g.
> WITH PackageIdByAll(_PackageID PRIMARY KEY NOT NULL)
> isn't legal.
>
> Right now my log's being spammed with oodles of automatic index messages
> because of the CTE. I'd like to *fix* this, but right now the only
> solution I'm seeing is suppressing all automatic index log messages. I'd
> like to still see REAL ones to not mask new issues.
>
> Suggestions?
>
>
>
> -----Original Message-----
> From: sqlite-users [mailto:[hidden email]]
> On Behalf Of Clemens Ladisch
> Sent: Friday, April 21, 2017 11:13 PM
> To: [hidden email]
> Subject: Re: [sqlite] 0x11C: automatic index warning for CTEs ?
>
> Howard Kapustein wrote:
> > I'm setting SQLite logging a warning
> > Warning 0x11C: automatic index on PackageIdByAll(_PackageID)
> >
> > WITH ...
> > PackageIdByAll(_PackageID) AS (...)
> > SELECT *
> >     FROM ...
> >     INNER JOIN PackageIdByAll AS cte_p ON
> > cte_p._PackageID=pkg._PackageID
> >
> > Why?
> >
> > PackageIdByAll is a CTE. I can't CREATE INDEX for it.
>
> The warning tells you that the database created an automatic index.
> This does not imply that you _must_ create an index, it's just a hint that
> you should think about whether it is possible to improve the query.
> If you have a table, creating the index explicitly is the easiest way, but
> for CTEs/views/subqueries, you might have to change the query itself, or
> the database schema, or determine that the automatic index is the best
> choice in your situation.
>
> > _PackageID is the primary key of the Package table So indexing should
> > be covered being the table's ROWID.
>
> When you use the rowid to look up a table row, you still don't know which
> CTE row that would correspond to.
>
>
> Regards,
> Clemens
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> https://na01.safelinks.protection.outlook.com/?url=http%3A%2F%2Fmailinglis
> ts.sqlite.org%2Fcgi-bin%2Fmailman%2Flistinfo%2Fsqlite-
> users&data=02%7C01%7Choward.kapustein%40microsoft.com%7C97a3efe7e6f7483a93
> ca08d48946c459%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C63628438442085
> 4952&sdata=6DPB0%2Bbtibn2aYYms6AILfHztq7gORwmdEq0uYcsaG0%3D&reserved=0
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
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: 0x11C: automatic index warning for CTEs ?

Clemens Ladisch
In reply to this post by Howard Kapustein
Howard Kapustein wrote:
>> you might have to change the query itself
>
> The question is how?

The database has estimated that even with the cost of creating the
temporary index, this way is the fastest.  So it's unlikely that there
is another way that would be an improvement.

Anyway, try using the CTE on the left side of a CROSS JOIN to force the
DB to use it as the outer table of the nested loop:
http://www.sqlite.org/lang_select.html#crossjoin

> Right now my log's being spammed with oodles of automatic index
> messages because of the CTE.

You could disabling logging for this query, or filter out these specific
messages.


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