"Perhaps there are other reasons to add INCLUDE that are less fringe-case?"
That would depend a bit on how SQLITE is implemented. For SQL Server data in INCLUDE columns would end up only on leaf level pages, where data from INDEXED columns would be repeated on intermediary pages as well. But this may or may not be a thing with SQLITE. The other use case is if you have an index with a simple non-unique indexed column, and then a whole bunch of clustered values which are also the same. This would mean long comparison trees that have to be run, even though the "is equals" answer was known by the first column. - Deon -----Original Message----- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of R Smith Sent: Thursday, March 2, 2017 5:00 AM To: sqlite-users@mailinglists.sqlite.org Subject: Re: [sqlite] Non-unique columns in unique index On 2017/03/02 2:29 PM, Deon Brewis wrote: > "This Query (for instance) will be exceedingly fast: > SELECT ExtraCol FROM Foo WHERE UniqueCol > range_start AND UniqueCol < > range_end" > > No, that's not covered. I've tried that before, that query is too slow when > it isn't covered - the table is many GB's and the result is needed in low > milliseconds. Also, I don't need an index directly on ExtraCol like specified > below for the ( WHERE ExtraCol = ...) case . It's not useful a useful lookup > by itself - only a useful result. So the only reason to include it in an > index is to make it covered. Ah yes, so you /do/ need every bit of work cycle saving you can get.... > "You cannot optimize for everything, pick your favourite thing and optimize > for that." > > This is a bit of a call to support INCLUDE columns in indexes. This > optimization is very easy in SQL Server using that. Well yes, so in SQLite you can optimize either for the speed or for the space, not for both. In MSSQL you can optimize for both using INCLUDE, and I can see how it is useful in specifically your case. That said, I doubt it would make it into development code for SQLite at this point for the simple reason that it adds weight to the engine (adding INCLUDE) for a very fringe-case optimization. Perhaps there are other reasons to add INCLUDE that are less fringe-case? _______________________________________________ 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
