Flash Website Builder- Trendy Site Builder is a Flash Site Building tool that helps users build stunning websites. Check Out Custom Custom Logo Design by LogoBee. Website Design and Free Logo Templates available.
 CodingForums.com Design question: numeric variable length to save space?
 User Name Remember Me? Password

Before you post, read our: Rules & Posting Guidelines

 Thread Tools Rate Thread
Enjoy an ad free experience by logging in. Not a member yet? Register.
 08-29-2013, 10:27 PM PM User | #1 Keleth Senior Coder   Join Date: Jun 2008 Location: New Jersey Posts: 2,476 Thanks: 45 Thanked 254 Times in 251 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.
 08-29-2013, 10:38 PM PM User | #2 Fou-Lu God Emperor     Join Date: Sep 2002 Location: Saskatoon, Saskatchewan Posts: 16,603 Thanks: 4 Thanked 2,598 Times in 2,567 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');  ```
 08-29-2013, 10:41 PM PM User | #3 djm0219 Senior Coder     Join Date: Aug 2003 Location: Wake Forest, North Carolina Posts: 1,276 Thanks: 4 Thanked 197 Times in 195 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
 08-29-2013, 10:41 PM PM User | #4 Keleth Senior Coder   Join Date: Jun 2008 Location: New Jersey Posts: 2,476 Thanks: 45 Thanked 254 Times in 251 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.
 08-29-2013, 10:44 PM PM User | #5 Keleth Senior Coder   Join Date: Jun 2008 Location: New Jersey Posts: 2,476 Thanks: 45 Thanked 254 Times in 251 Posts As a follow up then, what purpose is the length attribute, or is it simply redundancy?
08-29-2013, 10:45 PM   PM User | #6
djm0219
Senior Coder

Join Date: Aug 2003
Location: Wake Forest, North Carolina
Posts: 1,276
Thanks: 4
Thanked 197 Times in 195 Posts
Quote:
 Originally Posted by Keleth 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.
__________________
Dave .... HostMonster for all of your hosting needs

Last edited by djm0219; 08-29-2013 at 10:48 PM.. Reason: Removed incorrect info ... see next post

08-29-2013, 10:47 PM   PM User | #7
djm0219
Senior Coder

Join Date: Aug 2003
Location: Wake Forest, North Carolina
Posts: 1,276
Thanks: 4
Thanked 197 Times in 195 Posts
Quote:
 Originally Posted by Keleth As a follow up then, what purpose is the length attribute, or is it simply redundancy?
From the manual again

Quote:
 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

 08-29-2013, 10:52 PM PM User | #8 Keleth Senior Coder   Join Date: Jun 2008 Location: New Jersey Posts: 2,476 Thanks: 45 Thanked 254 Times in 251 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.
08-29-2013, 11:05 PM   PM User | #9
djm0219
Senior Coder

Join Date: Aug 2003
Location: Wake Forest, North Carolina
Posts: 1,276
Thanks: 4
Thanked 197 Times in 195 Posts
Quote:
 Originally Posted by Keleth 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 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

 08-29-2013, 11:11 PM PM User | #10 Keleth Senior Coder   Join Date: Jun 2008 Location: New Jersey Posts: 2,476 Thanks: 45 Thanked 254 Times in 251 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
 08-29-2013, 11:13 PM PM User | #11 djm0219 Senior Coder     Join Date: Aug 2003 Location: Wake Forest, North Carolina Posts: 1,276 Thanks: 4 Thanked 197 Times in 195 Posts Sorry, guess I'm missing what you're not understanding yet __________________ Dave .... HostMonster for all of your hosting needs
 08-29-2013, 11:17 PM PM User | #12 Keleth Senior Coder   Join Date: Jun 2008 Location: New Jersey Posts: 2,476 Thanks: 45 Thanked 254 Times in 251 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.
 08-29-2013, 11:37 PM PM User | #13 Keleth Senior Coder   Join Date: Jun 2008 Location: New Jersey Posts: 2,476 Thanks: 45 Thanked 254 Times in 251 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?
 08-30-2013, 12:31 AM PM User | #14 Old Pedant Supreme Master coder!     Join Date: Feb 2009 Posts: 24,955 Thanks: 75 Thanked 4,308 Times in 4,275 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.
08-30-2013, 12:35 AM   PM User | #15
Old Pedant
Supreme Master coder!

Join Date: Feb 2009
Posts: 24,955
Thanks: 75
Thanked 4,308 Times in 4,275 Posts
Quote:
 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.

 Bookmarks

 Thread Tools Rate This Thread Rate This Thread: 5 : Excellent 4 : Good 3 : Average 2 : Bad 1 : Terrible

 Posting Rules You may not post new threads You may not post replies You may not post attachments You may not edit your posts BB code is On Smilies are On [IMG] code is On HTML code is Off Forum Rules
 Forum Jump User Control Panel Private Messages Subscriptions Who's Online Search Forums Forums Home :: Client side development     JavaScript programming         DOM and JSON scripting         Ajax and Design         JavaScript frameworks         Post a JavaScript     HTML & CSS     XML     Flash & ActionScript         Adobe Flex     Graphics and Multimedia discussions     General web building         Site reviews         Building for mobile devices :: Server side development     Apache configuration     Perl/ CGI     PHP         Post a PHP snippet     MySQL         Other Databases     Ruby & Ruby On Rails     ASP     ASP.NET     Java and JSP     Other server side languages/ issues         ColdFusion         Python :: Computing & Sciences     Computer Programming     Computer/PC discussions     Geek News and Humour Web Projects and Services Marketplace     Web Projects         Small projects (quick fixes and changes)         Medium projects (new script, new features, etc)         Large Projects (new web application, complex features etc)         Unknown sized projects (request quote)         Vacant job positions         Looking for work/ for hire         Project collaboration/ partnership         Paid work offers and requests (Now CLOSED)     Career, job, and business ideas or advice     Domains, Sites, and Designs for sale         Domains for sale         Websites for sale         Design templates and graphics for sale :: Other forums     Member Offers     Forum feedback and announcements

All times are GMT +1. The time now is 02:29 AM.

 AdvertisementLog in to turn off these ads.