Varchar (5,000)?

[MatthewBOnline]

Hi,

I am going to use a single field in a MySQL table for content. This content will be over 1,000 words which must be over 5,000 characters. This content will have <p> tags and <div id=...> and <div class=...> tags for CSS Styling.

I think it is best to choose varchar (5,000). Or would you advise something else, before I start filling in the table?

Whatever loads fastest when using PHP is what I want to use!

Matt.

[chris.upjohn]

Personally i wouldn't recommend using VARCHAR for more then 255 characters as its not designed for it, the best option i can recommend is one of the TEXT options as they are designed for long strings.

[r937]

use TEXT

also, consider putting the actual copy into a separate table

e.g.

CREATE TABLE cms_entries
( id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT
, pagename VARCHAR(255) NOT NULL
, title VARCHAR(255) NOT NULL
, descr VARCHAR(255) NOT NULL
, url   VARCHAR(255) NOT NULL
, added DATETIME NOT NULL
, chged DATETIME NULL 
) ENGINE=InnoDB
;
CREATE TABLE cms_contents
( id INTEGER NOT NULL PRIMARY KEY 
, content TEXT NOT NULL
) ENGINE=MyISAM

there are two advantages:

when running queries against your entries only, like for a list consisting only of title and url, the rows are shorter so the queries are more efficient

also, having the one-to-one relationship with the actual text in a separate myisam table means that you can declare a fulltext index on it, while the main entries table remains innodb

[MatthewBOnline]

SgtLegend, I tried setting the "Length/Values" for a text field and noticed it does not save a maximum. I suppose this is because there is no maximum. I will probably use this method.


r937, I do not understand your reasoning. What are MYISAM and INNODB tables? I see you are creating tables. In other words you mean create two separate tables in MyPHPAdmin, right? But, if this is correct, how do I set the tables to MYISAM and INNODB? And what does it do to them and what are the benefits? Will it load faster?

Matt.

[r937]

sorry, can't help you with phpmyadmin, i hate that piece of cr@p

as for myisam versus innodb, perhaps you can do some googling, these are two different types of mysql engines

[Michael Morris]

I think it is best to choose varchar (5,000). Or would you advise something else, before I start filling in the table?

Personally i wouldn't recommend using VARCHAR for more then 255 characters as its not designed for it, the best option i can recommend is one of the TEXT options as they are designed for long strings.

Important Tip: If instructed to create a varchar field of any value over 255 MySQL will create a Text field instead.

[r937]

Important Tip: If instructed to create a varchar field of any value over 255 MySQL will create a Text field instead.

michael, your tip is valid only for versions up to 5.0.3

note: 5.1 went into production release in november 2008, and 5.5 in december 2010

you gots some catchin up to do, lucy

[Cups]

@r937 Great posting (#3) which applies to many of us who read this.

[LinuxFreelancer]

Personally i wouldn't recommend using VARCHAR for more then 255 characters as its not designed for it, the best option i can recommend is one of the TEXT options as they are designed for long strings.

Values in VARCHAR columns are variable-length strings. The length can be specified as a value from 0 to 255 before MySQL 5.0.3, and 0 to 65,535 in 5.0.3 and later versions. The effective maximum length of a VARCHAR in MySQL 5.0.3 and later is subject to the maximum row size (65,535 bytes, which is shared among all columns) and the character set used.

Source: MySQL :: MySQL 5.0 Reference Manual :: 10.4.1 The CHAR and VARCHAR Types

Using VarChar(300) for an example, is handy when creating indexes. If you use TEXT you might just end up not being able to, due to the size restriction for the INDEX.