Re: Is it inefficient to have two unique keys where the second is redundant?

On Tue, 2017-06-13 at 07:49 -0400, Glenn Gundermann wrote:

Hi there,

I saw some code last night that made me wonder.

One registration table had two unique keys:
The first unique key: (profile, event)
The second unique key: (event, profile)

I'm wondering if it's redundant to have the second unique key since the
first unique key will ensure this.

For performance, it makes sense to have an index for (event, profile).
Is there any extra overhead or inefficiency by making it a unique key?

My guess is that that its clever enough to know that the same keys are
used so it doesn't bother to do a second record/duplicate check. That
said, even if its not that clever, I wonder if the additional record key
test would overly affect performance as I would assume that its code
that has had years of optimisations applied since the "400" first hit
the market. I guess it could be tested by writing tens of thousands of
records with and without the unique, but my instinct says the time
differences would be quite small for a large number of records (and so
small for individual records that the impact would be effectively zero
in most programs). Be interesting to know for sure now that you've
brought it up.

Yours truly,

Glenn Gundermann
Email: glenn.gundermann@xxxxxxxxx
Work: (905) 486-1162 x 239
Cell: (416) 317-3144