>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:sqlite-users-boun...@mailinglists.sqlite.org] On
Behalf Of Clemens Ladisch
Sent: Friday, April 21, 2017 11:13 PM
To: sqlite-users@mailinglists.sqlite.org
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
sqlite-users@mailinglists.sqlite.org
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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
