Mysql – How bad is to have 3 unique constraints in a table

database-designMySQL

I have a table with articles. In this table I have article_title (varchar 500), article_meta_description (varchar 2000) and article_content (longtext). All of them are set to be unique (each of them, not together).

Why:

article_title because it's better for SEO

article_meta_description because it's better for SEO

article_content because I want to be sure there won't be unwanted duplicates made by a bad insert

I am aware that it may have an impact on insert performance, but I'm not sure about the size of impact. I also don't remember to have seen 3 unique columns in a single table somewhere.

So, how bad is what I'm doing ?

Best Answer

MySQL has a limit on index length. It is 3072 bytes, I think. That rules out having a UNIQUE on article_content. Another column is (up to) 2000 characters; it is rarely realistic to have UNIQUE on such a wide column.

UNIQUE, in MySQL, does two things:

  • Provide a uniqueness constraint, preventing duplicates from being inserted (unless NULL).
  • Provide an INDEX for rapid lookup.

Assuming you need the constraint, not the index, here is a technique to clumsily handle such:

Add another column, say, content_hash CHAR(32) CHARACTER SET ascii UNIQUE. Then stick MD5(article_content) in it. (This can be done either in app code, by a trigger, or by a "generated column".) And, because of the constraint, it will prevent insertion of identical 'content'.

Related Question