SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Evangelist
    Join Date
    Feb 2000
    Location
    England
    Posts
    568
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    choosing field type in mysql

    been reading mysql manual and trying to work out which field type to use. also peaking at the VB field types.

    1. CHAR v VARCHAR

    it appears this is a tradeoff between space and speed. is that correct? CHAR takes up all the possible space allocated whereas varchar does not. Varchar is bad if something is updated a lot because it leads to fragmentation. Is that essentially correct?

    2. INT, SMALLINT, MEDIUMINT.

    Can someone explain the difference between these. What is the difference between INT(3) and SMALLINT(3) for example?

    3. INT v ENUM.

    I notice that vbulletin stores settings variables in INT field types and not ENUM with a 1 or 0. any idea why? What is the best way to store a setting that can only be 0, 1 NOT NULL ?

    thank you!

  2. #2
    SitePoint Zealot
    Join Date
    May 2001
    Posts
    141
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    1. Yes, that is essentially correct.
    2.As a general rule, if you know that you have a set range of numbers to store in an INT type column (e.g.- won't ever exceed +255), as a general rule you should choose the type that corresponds to that range. If you aren't dealing with negative numbers, you can declare it as UNSIGNED which will increase your range by tagging the absolute value of the range of the negative values onto your positive range. This is in the MySQL documentation. Regardless, you'll never go wrong by choosing your INT types in this way, but I am not sure that it offers hugely significant differences in performance.
    3.I'm not sure that you can have NULL or NOT NULL values in an ENUM field, but I would have to check it out. I know that it is a pain sometimes to extract values from ENUMs, but I haven't had tons of experience with this stuff...I can check some of my MySQL books if you really want!

    Cheers,

    XGuy.


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
  •