What column data type should I use for storing large amounts of text or html

Question

I have a column in a table which used to be varchar(255) in the beginning and due to some design changes now it is varchar(1536) = 1024 + 512. I will not be searching or indexing this field, does it make sense to store this value in a different data type other than a varchar if you would like to optimize this for performance?

Answer 1

You should use TEXT like the others said, but there is some important advice every time you use TEXT or BLOB: decouple them form your base table as they really slow down accessing the table. Imagine the following structure:

CREATE TABLE article (     id INT(10) UNSIGNED,     title VARCHAR(40),     author_id INT(10) UNSIGNED,     created DATETIME,     modified DATETIME );  CREATE TABLE article_body (     id INT(10) UNSIGNED,     body TEXT ); 

Whenever you list articles you can use the article table (last 5 articles of author 33):

SELECT id, title FROM article WHERE author_id=33 ORDER BY created DESC LIMIT 5 

And when someone really opens the article you can use something like:

SELECT a.title, ab.body FROM article AS a    LEFT JOIN article_body AS ab ON ab.id = a.id WHERE a.id=82 

Answer 2

Yes, it will be better if you can store the values in the "TEXT" data type. For more details, please read this article.

Regarding knowledge of storage requirements, you can read this one.

Hope it helps.