Proposal Clarification. I see that discussion become too complicated. So, I'd like to clarify what we are talking about.
We are discussing 2 different improvements of index. The one is "partially unique index" and the other "index with included columns". Let's look at example.
- We have a table tbl(f1, f2, f3, f4). - We want to have an unique index on (f1,f2). - We want to have an index on (f1, f2, f3) which allow us to use index for complex "where" clauses.
Can someone write a query where F3 being ordered is a contribution?
If F1 and F2 are unique, adding F3 to a where or order by clause doesn't seem to contribute anything.
-- Already fully ordered by F1,F2
SELECT ... ORDER BY F1, F2, F3;
-- F3 isn't in a known order without specifying F2
SELECT ... WHERE F1 = ? ORDER BY F1, F3;
-- Index resolves to a single record; nothing to order
SELECT ... WHERE F1 = ? AND F2 = ? ORDER BY F3;
-- Without a where clause, the index isn't helpful unless F3 is the first column
