"Any Index that starts with a Unique column will by definition be Unique." Sorry, yet, I admit the title of the thread is confusing. What I meant to say is that it's TOO unique :). Adding the additional columns will allow duplicates on the columns where duplicates should not be allowed.
"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. "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. - Deon -----Original Message----- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of R Smith Sent: Thursday, March 2, 2017 2:50 AM To: sqlite-users@mailinglists.sqlite.org Subject: Re: [sqlite] Non-unique columns in unique index Any Index that starts with a Unique column will by definition be Unique. Of course in your case you want the Uniqueness of only the first column to be enforced, but you want to lookup also using the second column (either or both). Why insist on having it in a covering Index though? Why not just make one Unique index, and one other Index on the other column by itself? Like so: +CREATE TABLE Blah( Id Integer Primary Key, UniqueCol blob, // 20 bytes fixed ExtraCol blob, // 12 bytes fixed UNIQUE(UniqueCol) ); // ~36 bytes/row CREATE INDEX Blah_ExtraIdx on Blah(ExtraCol); This will be close to the same size of one covering Index. The Query planner is clever enough to figure out it can use only the second Index to look up some queries, it can also use the Unique Index if needed for a query and it will use it to enforce uniqueness. You get all the speed and a little extra overhead space, but much less than a covering Index. This Query (for instance) will be exceedingly fast: SELECT ExtraCol FROM Foo WHERE UniqueCol > range_start AND UniqueCol < range_end So would this: SELECT * FROM Foo WHERE UniqueCol > range_start AND UniqueCol < range_end AND ExtraCol = xxx; And this: SELECT * FROM Foo WHERE ExtraCol = xxx; All of these will be super fast and your Indices will take up the least possible space. There is some saving in cycles if you can read the bytes directly out of a covering Index rather than a big table, but this is no big table, it should be real quick. If you really really really need those few cycles saved, invest the MBs and make the covering Index additional. If space is a problem, use only the single Unique index. You cannot optimize for everything, pick your favourite thing and optimize for that. Cheers, Ryan On 2017/03/01 7:00 PM, Deon Brewis wrote: > Is there way to add non-unique columns in a unique index? > > I would like to use the same index to enforce unique constraints, as well as > giving a covered result for other queries. > > Something like an 'INCLUDE' would also work (actually even better). E.g. > > CREATE UNIQUE INDEX indx ON Foo(UniqueCol) INCLUDE (ExtraCol) > > > If not, is there a way to efficiently implement a UNIQUE constraint in > a different way? (Trigger maybe?) > > -Deon > > _______________________________________________ > 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 _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
