Beefy Boxes and Bandwidth Generously Provided by pair Networks
"be consistent"
 
PerlMonks  

MySQL: BLOB/TEXT vs multiple VARCHAR columns

( #28609=categorized question: print w/ replies, xml ) Need Help??
Contributed by rodry on Aug 19, 2000 at 02:01 UTC
Q&A  > database programming


Description:

I have the following scenario (which you may be familiar with:

An online periodical is needed, similar in fashion to Slashdot and others. I suggested the best solution for it to be PERL/MySQL given the available resources ($).

Doing research I on development of similar sites I notice that one way to store an article/news text was to create several VARCHAR columns with the maximun of 255 characters for each one. The author would then break up the text to be posted into several chunks, 255 character long.

Then, upon reading the MySQL documentation in more detail, I noticed that it supports BLOB/TEXT column types that seemed to be specially suited for the kind of application I am dealing with.

So, the obvious question is, which is the preffered way to attack such problem. Or better yet, what are the pros and cons of each approach.

Thank you Monks.

Answer: MySQL: BLOB/TEXT vs multiple VARCHAR columns
contributed by athomason

All string types are equivalent as far as your SQL is concerned; the only difference is how much data they can store. You can see a comparison of the size requirements of all the types here. Like you say, VARCHAR can only up to 255 characters. LONGTEXT can hold up to 4GB of data, assuming you have the available disk space. You're probably fine with a MEDIUMTEXT, which holds up to 16MB, and it will only cost you two more bytes per record than VARCHAR. And obviously, using larger fields frees you of worrying about artificially splitting and combining text.

If you're not doing this for the Perl experience but just want a working site, you can steal the GPL'ed Slashcode.

Answer: MySQL: BLOB/TEXT vs multiple VARCHAR columns
contributed by lachoy

Unlike many database systems out there, the overhead for BLOB/TEXT fields in MySQL (I normally just use TEXT) is practically none. Other databases (Sybase, IME) allocate at minimum a 2kb page for each TEXT field in a table, even if the TEXT field is empty. Eeek!

Answer: MySQL: BLOB/TEXT vs multiple VARCHAR columns
contributed by cianoz

Blob/TEXT fields have some limitation on mysql: (compared with VARCHAR)
have a look at the documentation for more details.

Please (register and) log in if you wish to add an answer



  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • Outside of code tags, you may need to use entities for some characters:
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.
  • Log In?
    Username:
    Password:

    What's my password?
    Create A New User
    Chatterbox?
    and the web crawler heard nothing...

    How do I use this? | Other CB clients
    Other Users?
    Others romping around the Monastery: (8)
    As of 2014-09-16 01:31 GMT
    Sections?
    Information?
    Find Nodes?
    Leftovers?
      Voting Booth?

      My favorite cookbook is:










      Results (155 votes), past polls