Re: [sqlite] 0x11C: automatic index warning for CTEs ?
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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] 0x11C: automatic index warning for CTEs ?
Change UNION ALL to UNION. -- ˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı > -Original Message- > From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] > 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: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%2Fmailinglis > ts.sqlite.org%2Fcgi-bin%2Fmailman%2Flistinfo%2Fsqlite- > users=02%7C01%7Choward.kapustein%40microsoft.com%7C97a3efe7e6f7483a93 > ca08d48946c459%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C63628438442085 > 4952=6DPB0%2Bbtibn2aYYms6AILfHztq7gORwmdEq0uYcsaG0%3D=0 > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
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: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=02%7C01%7Choward.kapustein%40microsoft.com%7C97a3efe7e6f7483a93ca08d48946c459%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C636284384420854952=6DPB0%2Bbtibn2aYYms6AILfHztq7gORwmdEq0uYcsaG0%3D=0 ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
