SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Evangelist
    Join Date
    Mar 2011
    Posts
    423
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Varchar (5,000)?

    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.

  2. #2
    SitePoint Wizard bronze trophy chris.upjohn's Avatar
    Join Date
    Apr 2010
    Location
    Melbourne, AU
    Posts
    2,189
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    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.

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    use TEXT

    also, consider putting the actual copy into a separate table

    e.g.
    Code:
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Evangelist
    Join Date
    Mar 2011
    Posts
    423
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    I solve practical problems. bronze trophy
    Michael Morris's Avatar
    Join Date
    Jan 2008
    Location
    Knoxville TN
    Posts
    2,023
    Mentioned
    62 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by MatthewBOnline View Post
    I think it is best to choose varchar (5,000). Or would you advise something else, before I start filling in the table?
    Quote Originally Posted by SgtLegend View Post
    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.

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Michael Morris View Post
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    @r937 Great posting (#3) which applies to many of us who read this.

  9. #9
    SitePoint Zealot LinuxFreelancer's Avatar
    Join Date
    Jun 2011
    Location
    Boston, Ma, Usa
    Posts
    116
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by SgtLegend View Post
    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.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •