MySQL 5.0 indexes – Unique vs Non Unique

by

UNIQUE and PRIMARY KEY are constraints, not indexes. Though most databases implement these constraints by using an index. The additional overhead of the constraint in addition to the index is insignificant, especially when you count the cost of tracking down and correcting unintentional duplicates when (not if) they occur.

Indexes are usually more effective if there you have a high selectivity. This is the ratio of number of distinct values to the total number of rows.

For example, in a column for Social Security Number, you may have 1 million rows with 1 million distinct values. So the selectivity is 1000000/1000000 = 1.0 (although there are rare historical exceptions, SSN’s are intended to be unique).

But another column in that table, “gender” may only have two distinct values over 1 million rows. 2/1000000 = very low selectivity.

An index with a UNIQUE or PRIMARY KEY constraint is guaranteed to have a selectivity of 1.0, so it will always be as effective as an index can be.

You asked about the difference between a primary key and a unique constraint. Chiefly, it’s that you can have only one primary key constraint per table (even if that constraint’s definition includes multiple columns), whereas you can have multiple unique constraints. A column with a unique constraint may permit NULLs, whereas columns in primary key constraints must not permit NULLs. Otherwise, primary key and unique are very similar in their implementation and their use.

You asked in a comment about whether to use MyISAM or InnoDB. In MySQL, they use the term storage engine. There are bunch of subtle differences between these two storage engines, but the chief ones are:

  • InnoDB supports transactions, so you can choose to roll back or commit changes. MyISAM is effectively always autocommit.
  • InnoDB enforces foreign key constraints. MyISAM doesn’t enforce or even store foreign key constraints.

If these features are things you need in your application, then you should use InnoDB.

To respond to your comment, it’s not that simple. InnoDB is actually faster than MyISAM in quite a few cases, so it depends on what your application’s mix of selects, updates, concurrent queries, indexes, buffer configuration, etc.

See http://www.mysqlperformanceblog.com/2007/01/08/innodb-vs-myisam-vs-falcon-benchmarks-part-1/ for a very thorough performance comparison of the storage engines. InnoDB wins over MyISAM frequently enough that it’s clearly not possible to say one is faster than the other.

As with most performance-related questions, the only way to answer it for your application is to test both configurations using your application and a representative sample of data, and measure the results.

More Related Contents:

  1. With MySQL, how can I generate a column containing the record index in a table?
  2. How do I add indexes to MySQL tables?
  3. Differences between INDEX, PRIMARY, UNIQUE, FULLTEXT in MySQL?
  4. When should I use a composite index?
  5. Is the primary key automatically indexed in MySQL?
  6. MySQL indexes – what are the best practices?
  7. Mysql::Error: Specified key was too long; max key length is 1000 bytes
  8. How to see indexes for a database or table in MySQL?
  9. Speeding up row counting in MySQL
  10. What is cardinality in MySQL?
  11. MySQL varchar index length
  12. What’s the difference between using INDEX vs KEY in MySQL?
  13. Mysql Improve Search Performance with wildcards (%%)
  14. How can I manipulate MySQL fulltext search relevance to make one field more ‘valuable’ than another?
  15. How do I get MySQL to use an INDEX for view query?
  16. DOs and DONTs for Indexes [closed]
  17. MySQL performance: multiple tables vs. index on single table and partitions
  18. Is there any performance gain in indexing a boolean field?
  19. How to disable index in innodb
  20. Higher cardinality column first in an index when involving a range?
  21. Does Order of Fields of Multi-Column Index in MySQL Matter
  22. Create an index on a huge MySQL production table without table locking
  23. MySQL Integer vs DateTime index
  24. Does a UNIQUE constraint automatically create an INDEX on the field(s)?
  25. How to fix MySql: index column size too large (Laravel migrate)
  26. MySQL optimizing INSERT speed being slowed down because of indices
  27. Would it help to add index to BIGINT column in MySQL?
  28. MySQL – why not index every field?
  29. Mysql performance on 6 million row table
  30. Workaround in mysql for partial Index or filtered Index?

Leave a Comment