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
