Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2008
    Posts
    186

    Unanswered: Performance gain of adding UNIQUE constraint

    I have a table called machines where I have an ID field, a name field, and some other fields relating to the specific machine (who owns it, what it's used for etc.)

    I know the machine names will always be unique, however I've used a surrogate primary key anyway, since it's possible a machine's name could change (it'll still be unique, but it could change to something else)

    My question:

    How much of a performance/speed gain will I actually get by applying a UNIQUE constraint to the name? (a string column)

  2. #2
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    You do not gain anything in performance with a UNIQUE constraint. However, because UNIQUE constraints are enforced by indexes, you can gain some performance with this index, if it is used in WHERE clauses of your queries (like any other index).

    But without that, a UNIQUE constraint cause some loss in speed (index inserts, updates, deletes / constraint checking).

  3. #3
    Join Date
    Jan 2008
    Posts
    186
    Quote Originally Posted by aflorin27
    You do not gain anything in performance with a UNIQUE constraint. However, because UNIQUE constraints are enforced by indexes, you can gain some performance with this index, if it is used in WHERE clauses of your queries (like any other index).

    But without that, a UNIQUE constraint cause some loss in speed (index inserts, updates, deletes / constraint checking).
    So essentially, a UNIQUE constraint enforced with an index will cause (significant? minor?) gains in SELECT queries, but could down inserts/deletes/updates.

    Is that correct?

  4. #4
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    See this article:
    http://www.databasejournal.com/featu...le.php/1466951

    especially the How to Select Indexes for Optimal Database Performance section.

  5. #5
    Join Date
    Jan 2008
    Posts
    186
    Thanks! That looks like a great read for me

    Cheers

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •