2

The index are on tuples, ordered sets, of columns, but the UNIQUE constraint or qualifiant for an index is on (unordered) sets of columns.

I have MySQL/MariaDB in mind but my questions certainly applies to other DBMS.

If I have a table T with two columns id1, id2 and two index on this two columns:

INDEX idx_id1_id2 (id1, id2)
INDEX idx_id2_id1 (id2, id1)

and I want to declare to the DBMS that the multi-set on all records of the table T of all couples (value(id1), value(id2)), or equivalently all couples (value(id2), value(id1)), is "UNIQUE" (this multi-set is a set),

I can change 

INDEX idx_id1_id2 (id1, id2)
INDEX idx_id2_id1 (id2, id1)

for

UNIQUE INDEX idx_id1_id2 (id1, id2)
INDEX idx_id2_id1 (id2, id1)

But I can also declare :

UNIQUE INDEX idx_id1_id2 (id1, id2)
UNIQUE INDEX idx_id2_id1 (id2, id1)

Is there some DBMS that deduce from 

UNIQUE INDEX idx_id1_id2 (id1, id2)
INDEX idx_id2_id1 (id2, id1)

that the second index is also unique?

Is there some DBMS that deduce from 

UNIQUE INDEX idx_id1_id2 (id1, id2)
UNIQUE INDEX idx_id2_id1 (id2, id1)

that they don't have to do twice the unicity check on inserting/updating value in the table ?

From a declarative point of view, I prefer

UNIQUE INDEX idx_id1_id2 (id1, id2)
UNIQUE INDEX idx_id2_id1 (id2, id1)

but however there may be a performance penalty on insert/update.

If there is a performance penalty for declaring UNIQUE on many indexes, will it be significant ?

Is there any (significant) performance boost for some queries with the second UNIQUE qualifiant added ?

Improve this question

2 Answers 2

Reset to default
1

Case 1 If you need to avoid both (1,2) and (2,1), then nothing in the Question will prevent that form of "dup".

To do that, always store the LESSER() value in id1 and the GREATER() in id2. Then UNIQUE(id1, id2) prevents dups.

Meanwhile, do a similar sort of the two values when doing a lookup.

Then you have only the one UNIQUE key -- which may as well be the PRIMARY KEY. Now you have

  • Uniqueness constraint that works
  • Fewer indexes
  • Only need to do one lookup, not two

etc

Case 2 If (1,2) and (2,1) can coexist, then the order of the ids presented to the table is important. Probably all you need is 

PRIMARY KEY(id1, id2)

If you have something like WHERE id1 BETWEEN .. AND .. AND id2 = 7, then you would also need

INDEX(id2, id1)

My Cookbook covers most of these simply possibilities.

Improve this answer
3
  • Thanks for your answer. I'm in case 2 of your answer. An index on (id1, id2) is not sufficient since some queries may require index on id2 alone or more rarely (id2, id1) in this order. Think about the following examples "WHERE id2 = 1", "WHERE id1 = 3 AND id2 BETWEEN 100 AND 1000000", "WHERE id1 BETWEEN 1 AND 300000 AND id2 = 7". For me it is clear that INDEX (id2, id1) will be more efficient for the first and third "WHERE", while INDEX (id1, id2) will be better for second "WHERE". The question is really on the UNIQUE qualifier. Does it speed up queries? How clever are DBMS about UNIQUE?
    – Laurent Lyaudet
    Mar 11, 2018 at 12:52
  • @LaurentLyaudet - UNIQUE does not significantly speed up SELECTs; it does slow down INSERTs a little. Go with the business logic -- say UNIQUE only when you need a uniqueness constraint.
    – Rick James
    Mar 11, 2018 at 16:11
  • @LaurentLyaudet - I agree with your indexes for the sample WHEREs in your Comment. Make the more frequently used one the PK, make the other a plain INDEX.
    – Rick James
    Mar 11, 2018 at 16:15
-2

Yes you should. Index for unique data need qualifier UNIQUE because that determine which kind of tree structure will be used. Sure you can achieve uniqness of fields combination by single index of UNIQUE type but unique index itself is somehow faster than non-unique one. If an index can be unique or non-unique - make it unique.

Improve this answer
4
  • Thanks for the answer. Can you quantify the "somehow faster" relatively to some DBMS and typical use case?
    – Laurent Lyaudet
    Mar 8, 2018 at 20:34
  • 1
    No. MySQL uses BTree structure for both INDEX and UNIQUE. (Also for the PRIMARY KEY, which is clustered with the data.) UNIQUE adds a constraint, which has nothing to do with the structure. That constraint is applied during INSERT to check for dup key. Making it unnecessarily UNIQUE, will slow down INSERTs.
    – Rick James
    Mar 9, 2018 at 14:39
  • What about other DBMS? Do they use the same tree structure?
    – Laurent Lyaudet
    Mar 11, 2018 at 12:59
  • Other DBMS may use other structures, and don't necessarily "cluster" the PK with the data. So, what I say applies to MySQL/MariaDB, not necessarily other engines.
    – Rick James
    Mar 11, 2018 at 16:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.