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

Question

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

Leave a Comment Cancel reply