Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    Senior Coder
    Join Date
    Jun 2008
    Location
    New Jersey
    Posts
    2,536
    Thanks
    45
    Thanked 259 Times in 256 Posts

    Design question: numeric variable length to save space?

    I'm a bit confused by this, so hopefully I can get some clarification.

    A int, by default, is 11 characters long. When an entry is created with an int column, does the db set aside space for an 11 character number, even if I set the length to 2 (I know, tinyint). Basically, if I have something like a tinyint at 2, does it save space? or does it just limit what can be entered, which I'm already restricting in PHP.

  • #2
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    No that's completely wrong. Numbers are *not* measured in characters, they are measured in bits. An integer is 32-bits in length or the equivalent of 4 chars. You can fit 4,294,967,295 in an unsigned integer, but you cannot fit 4,294,967,296 or 6,927,694,924 for examples.
    The "length" property in the numeric sizing refers to 0-pad. There would be no difference in size between an int(2) and an int(10); these are both 32 bits in length.
    Tinyint would save space to an int though as a tiny int is 8-bits in size. So if you can fit what you need between [-128, 127] or [0, 255] unsigned, than use a tinyint.
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 
    Been gone for a few months, and haven't programmed in that long of a time. Meh, I'll wing it ;)

  • #3
    Senior Coder djm0219's Avatar
    Join Date
    Aug 2003
    Location
    Wake Forest, North Carolina
    Posts
    1,303
    Thanks
    4
    Thanked 204 Times in 201 Posts
    From the MySQL manual on how many bytes each type takes:

    Type (Bytes)
    TINYINT 1
    SMALLINT 2
    MEDIUMINT 3
    INT 4
    BIGINT 8

    The length you specify applies to the amount of space used when displaying the value not how large or small the value may be.
    Dave .... HostMonster for all of your hosting needs

  • #4
    Senior Coder
    Join Date
    Jun 2008
    Location
    New Jersey
    Posts
    2,536
    Thanks
    45
    Thanked 259 Times in 256 Posts
    You're absolutely right, I didn't mean a literal character length in data, I meant in what's stored. When you store a int(6), doesn't it only allow 6 characters to store? Sorry if I phrased that strangely.

  • #5
    Senior Coder
    Join Date
    Jun 2008
    Location
    New Jersey
    Posts
    2,536
    Thanks
    45
    Thanked 259 Times in 256 Posts
    As a follow up then, what purpose is the length attribute, or is it simply redundancy?

  • #6
    Senior Coder djm0219's Avatar
    Join Date
    Aug 2003
    Location
    Wake Forest, North Carolina
    Posts
    1,303
    Thanks
    4
    Thanked 204 Times in 201 Posts
    Quote Originally Posted by Keleth View Post
    You're absolutely right, I didn't mean a literal character length in data, I meant in what's stored. When you store a int(6), doesn't it only allow 6 characters to store?
    No, an int is only going to take up 4 bytes no matter what.
    Last edited by djm0219; 08-29-2013 at 09:48 PM. Reason: Removed incorrect info ... see next post
    Dave .... HostMonster for all of your hosting needs

  • #7
    Senior Coder djm0219's Avatar
    Join Date
    Aug 2003
    Location
    Wake Forest, North Carolina
    Posts
    1,303
    Thanks
    4
    Thanked 204 Times in 201 Posts
    Quote Originally Posted by Keleth View Post
    As a follow up then, what purpose is the length attribute, or is it simply redundancy?
    From the manual again

    MySQL supports an extension for optionally specifying the display width of integer data types in parentheses following the base keyword for the type. For example, INT(4) specifies an INT with a display width of four digits. This optional display width may be used by applications to display integer values having a width less than the width specified for the column by left-padding them with spaces. (That is, this width is present in the metadata returned with result sets. Whether it is used or not is up to the application.)

    The display width does not constrain the range of values that can be stored in the column. Nor does it prevent values wider than the column display width from being displayed correctly. For example, a column specified as SMALLINT(3) has the usual SMALLINT range of -32768 to 32767, and values outside the range permitted by three digits are displayed in full using more than three digits.
    Dave .... HostMonster for all of your hosting needs

  • #8
    Senior Coder
    Join Date
    Jun 2008
    Location
    New Jersey
    Posts
    2,536
    Thanks
    45
    Thanked 259 Times in 256 Posts
    Ok, so if I have a int(4), I can store 12345, but it will only show 2345?

    And again, understand an int takes up a certain amount of space, I meant what actually gets stored. I'm having trouble coming up with the right terms here, as I seem to be coming up with the same terms for both storage space and the data that gets stored.

  • #9
    Senior Coder djm0219's Avatar
    Join Date
    Aug 2003
    Location
    Wake Forest, North Carolina
    Posts
    1,303
    Thanks
    4
    Thanked 204 Times in 201 Posts
    Quote Originally Posted by Keleth View Post
    Ok, so if I have a int(4), I can store 12345, but it will only show 2345?
    No, it will show the full value. And having that (4) doesn't really mean anything unless whatever program you're using it with recognizes it and does something with it.

    If you're going to be using PHP it has no bearing on it at all. You're always going to get the value back and how you display it is totally up to you.

    Quote Originally Posted by Keleth View Post
    And again, understand an int takes up a certain amount of space, I meant what actually gets stored.
    What gets stored is a collection of bits that equal the number being stored. It's a little hard to show it here but lets try a simple value like 12. Each of the zeros and ones below is a bit and we'll show a full byte (8 bits).

    0000 1100

    Going right to left the bit values are:

    1 2 4 8 16 32 64 128

    If we flip that to match our byte we get

    128 64 32 16 8 4 2 1

    Since the 8 bit and 4 bit are "on" the value is 12.
    Dave .... HostMonster for all of your hosting needs

  • #10
    Senior Coder
    Join Date
    Jun 2008
    Location
    New Jersey
    Posts
    2,536
    Thanks
    45
    Thanked 259 Times in 256 Posts
    Yah, I'm pretty well versed in binary, I studied assembly for a while

    I just can't seem to come up with the right terms here to not sound like an idiot

  • #11
    Senior Coder djm0219's Avatar
    Join Date
    Aug 2003
    Location
    Wake Forest, North Carolina
    Posts
    1,303
    Thanks
    4
    Thanked 204 Times in 201 Posts
    Sorry, guess I'm missing what you're not understanding yet
    Dave .... HostMonster for all of your hosting needs

  • #12
    Senior Coder
    Join Date
    Jun 2008
    Location
    New Jersey
    Posts
    2,536
    Thanks
    45
    Thanked 259 Times in 256 Posts
    I think you've answered it: the length attribute in mysql doesn't seem to really have any meaning, if I understand what you're saying, specially in PHP. If I set a int length 2, it doesn't restrict it to a 2 digit integer. And as it doesn't limit the amount of data stored (as you've shown, its still storing 4 bytes (unless I'm off about that number?)), so in this case, I don't need to go back over my database to make sure the lengths are appropriate for the incoming data. I should just use the appropriate type to save space.

  • #13
    Senior Coder
    Join Date
    Jun 2008
    Location
    New Jersey
    Posts
    2,536
    Thanks
    45
    Thanked 259 Times in 256 Posts
    And actually, for that matter, since bools store as a tinyint, it doesn't matter if they're length 4 or 1, does it? Kinda silly there isn't a bool type, no?

  • #14
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,947
    Thanks
    79
    Thanked 4,424 Times in 4,389 Posts
    The places that is mainly affected by the use of crap like INT(6), et al., are mainly DB tools that use those widths to decide how wide to display a column. But not many tools, even, actually use them. Most tools, including even the MySQL command line client, instead figure out what the max width needed is for each text representation of a column and use that.

    MySQL *does* allow a BOOLEAN type, by the by.
    Code:
    CREATE TABLE keleth ( v1 boolean );
    But if you then ask to DESCRIBE keleth you will see that as
    Code:
    mysql> describe keleth;
    +-------+------------+------+-----+---------+-------+
    | Field | Type       | Null | Key | Default | Extra |
    +-------+------------+------+-----+---------+-------+
    | v1    | tinyint(1) | YES  |     | NULL    |       |
    +-------+------------+------+-----+---------+-------+
    So boolean and tinyint are really synonyms.

    MySQL also allows you to insert boolean values: true and false. But those are actually just aliases for the integer values 1 and 0. (And, yes, because boolean and tinyint are synonyms, you can store 127 [for example] in a supposedly boolean field.)

    It's really a dumb alias on MySQL's part. It would have made much more sense to have boolean be an alias for bit(1) [and the width *IS* meaningful for bit fields!!!] so that, indeed, you could only store true and false into a boolean field. But I think that the bit type was added late in MySQL's life, and by then it was considered too late to change what boolean meant.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #15
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,947
    Thanks
    79
    Thanked 4,424 Times in 4,389 Posts
    I should just use the appropriate type to save space.
    No.

    Just use INT. Forget about TINYINT, SMALLINT, MEDIUMINT.

    First of all, you may not save any space at all, because the fields still have to be placed on appropriate boundaries (for those hardware architectures that require it). Secondly, how much space will you really save? Unless you have a billion records in your table, who cares if you save (say) 13 bytes per record???? Surely you are wasting much more than that in improperly using VARCHAR or, especially, TEXT.

    Keep it simple. ONLY if you find that you really are running out of disk space (extremely unlikely!) would you worry about saving bytes. "Penny wise, pound foolish" as the expression so aptly goes.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  
    Page 1 of 2 12 LastLast

    Posting Permissions

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