SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    Wanna-be Apple nut silver trophy M. Johansson's Avatar
    Join Date
    Sep 2000
    Location
    Halmstad, Sweden
    Posts
    7,400
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Column types and DB size

    I'm sick today, which makes me incredibly tired, and unable to do any real work, so I sit around skimming my book on mySQL from New Riders.

    I'm sitting around reading about the difference between CHAR and VARCHAR, and how you save space by using CHAR instead of VARCHAR if your strings are basically the same length:

    "If the values are all the same length, VARCHAR will actually use more space due to the extra byte used to record the length of values."
    WOW! I SAVE ONE BYTE PER ROW. That means that I save a whoopin 1MB of space per 1 million rows or so. Now, as I'm eager to learn, I wonder why databases still bother with this stuff in the age of cheap 120GB-harddrives? Does it somehow make querying faster or something?
    Mattias Johansson
    Short, Swedish, Web Developer

    Buttons and Dog Tags with your custom design:
    FatStatement.com

  2. #2
    Sultan of Ping jofa's Avatar
    Join Date
    Mar 2002
    Location
    SvÝ■jˇ­
    Posts
    4,080
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Isn't it the author of the book on mySQL that is bothered with the saving space stuff, not the databases?

    If you have more than one column of type varchar, and change them to char, then you save num_of_varchar_cols * num_of_rows bytes

    I think the char type must be faster when inserting/updating too, because you (or the database) know the length is always the same
    Last edited by jofa; Sep 11, 2002 at 10:06.

  3. #3
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,294
    Mentioned
    123 Post(s)
    Tagged
    1 Thread(s)
    Chars are faster than varchars. They are also much better for use in indexing.

    However, if the data you are storing is not exactly the same length each and everytime, you will not only be wasting space for each byte of data that is on the row, you will also have to trim the data each time you access it.
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style
    Learn how to be ready for The Forums' Move to Discourse

  4. #4
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Chances are you won't see a difference. Design for your data.

    Also remember that CHAR is fixed length, so if your CHARS are at all less than max you immediately have far less storage costs right there, which is why the 'saves one byte' makes no sense to me. SIZEOF( Data ) + 1 is typically much less than SIZEOF( Column Max ).

    There are several performance implications of wider rows which should be fairly evident. Also note that the speed decrease may be due to MySQL limitations/inefficienices and should not be held as a 'rule of thumb'.

  5. #5
    The doctor is in... silver trophy MarcusJT's Avatar
    Join Date
    Jan 2002
    Location
    London
    Posts
    3,509
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'm not sure whether it's relevant to MySQL or not, but in Access and SQL Server, text/memo (i.e. char/varchar) fields are usually stored as Unicode, which uses 2 bytes per character. If you're only dealing with standard ASCII text, changing the string format to this will halve the space used to store each string, which in a decent-sized database, is VERY significant. Naturally, this also simplifies text manipulation for the database engine, and so many queries will execute significantly faster too!!!
    MarcusJT
    - former ASP web developer / former SPF "ASP Guru"
    - *very* old blog with some useful ASP code

    - Please think, Google, and search these forums before posting!


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
  •