0

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 ?

2 Answers 2

2

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'.

0

There’s absolutely nothing wrong with this at all. Don’t try to optimize for insert performance unless you know you have a truly high throughput (think, thousands of individual inserts in a minute) use case.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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