SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,761
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Choosing Text-Field Size

    Many years ago, I recall being told that you should choose Text Field Sizes in certain increments for performance, but I don't remember the formula to use...

    It had something to do with matching the Field Size with how the Database stores things on the back end, and was multiples of something...

    So, as far as MySQL and VARCHAR() goes, is there such a formula?

    For example, would VARCHAR(64) be better than VARCHAR(59) of VARCHAR(65)?

    Thanks,


    Debbie

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by DoubleDee View Post
    For example, would VARCHAR(64) be better than VARCHAR(59) of VARCHAR(65)?
    if you don't really care, make all of them VARCHAR(255)

    if you do care, i.e. you want the system to tell you when it has truncated an input string to make it fit within the maximum of 64 or 59 or whatever you need, then yes, tailor it to the exact maximum you need
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,761
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    if you don't really care, make all of them VARCHAR(255)

    if you do care, i.e. you want the system to tell you when it has truncated an input string to make it fit within the maximum of 64 or 59 or whatever you need, then yes, tailor it to the exact maximum you need
    So, in other words, I hear you saying that it doesn't matter... (Maybe that was with Oracle that I was thinking about?!)

    All the manual seems to say is that for normal characters, VARCHAR() takes the String Length + 1 Byte.

    So I believe that means a VARCHAR(59) with "debbie" in it would be 7 Bytes, and a VARCHAR(64) with "debbie" in it would also be 7 Bytes, right?


    Debbie

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    google "mysql VARCHAR size"

    look for the stackoverflow pages that contain a discussion on what happens when a result set is built (e.g. when sorting is required)

    took me less than 30 seconds to find that there might be a performance issue if you have a VARCHAR defined too big and the result set returns many many rows

    for small result sets i don't think it makes a difference
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •