Database Help
 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
User Name:
Password:
Remember me



Go Back   Codewalkers ForumsOther TechnologiesDatabase Help

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
Thread Tools Search this Thread Rate Thread Display Modes
 
Unread Codewalkers Forums Sponsor:
Old October 22nd, 2007, 12:20 AM
macgyver07 macgyver07 is offline
Registered User
Codewalkers Newbie (0 - 499 posts)
 
Join Date: Oct 2007
Posts: 9 macgyver07 User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 1 h 12 m 18 sec
Reputation Power: 0
Question MySQL 4.1 - When to use varchar/integer/text?

I know that you select "integer" if the field only stores numbers, but what is the difference between varchar and text, longtext, tinytext etc? And some of the other fields. How does each affect performance?

Also length. How does this affect performance?
Thanks :-)

Reply With Quote
Old October 22nd, 2007, 02:03 PM
jcarouth's Avatar
jcarouth jcarouth is offline
Contributing User
Codewalkers Newbie (0 - 499 posts)
 
Join Date: Apr 2007
Location: College Station, TX
Posts: 57 jcarouth User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 1 Day 14 h 40 m 15 sec
Reputation Power: 8
Send a message via AIM to jcarouth Send a message via MSN to jcarouth Send a message via Yahoo to jcarouth
well think about it. if you store a number (in this case i am meaning integer) in a character type column you require the mysql engine to implicitly cast the value to match the column.

now on to character types. do you know the difference between CHAR and VARCHAR? you might try looking in the manual to better understand the data types. however the difference between varchar, text, longtext, and tinytext is the size. a VARCHAR(n) column can have a maximum n characters, but does not always take up n bytes. a CHAR(n) column can have a maximum n character but is padded so it should always be n bytes.

TINYTEXT has a maximum of 255 character, TEXT 65,535 characters, MEDIUMTEXT 16,777,215 characters and LONGTEXT 4,294,967,295 (could be less depending on server config). if you give TEXT(n) the smallest "TEXT" type (TINY, MEDIUM, TEXT, or LONG) that can hold n characters will be used.

i would suggest you go read about how data types work, which ones to choose, and how they will affect performance. it requires a lot more typing than i, or probably anyone, is willing to do in a forum.

NOTE: a common misconception is that INT(11) will make an integer have a size of 11 number places (up to 10,000,000,000 if unsigned) however this is not the case. the 11 in the example above is the format specifier and if the column is zero filled, 11 spaces will be filled. the size of the column is still the minimum and maximum values for the INTEGER type.

while we are on the topic of types, using the FLOAT type to represent data that you will perform calculations upon is a recipe for disaster. for data such as prices use the DECIMAL type instead. floating point precision could cause you to pull your hair out, i've seen it happen ;)

hope that helps to clear things up if not but a little.
__________________
carouth.com

Reply With Quote
Old October 22nd, 2007, 11:14 PM
macgyver07 macgyver07 is offline
Registered User
Codewalkers Newbie (0 - 499 posts)
 
Join Date: Oct 2007
Posts: 9 macgyver07 User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 1 h 12 m 18 sec
Reputation Power: 0
Thanks a lot for the effort! I'm still a little confused on what you mean by int(11) not standing for 11 values...

Reply With Quote
Old October 23rd, 2007, 12:32 AM
jcarouth's Avatar
jcarouth jcarouth is offline
Contributing User
Codewalkers Newbie (0 - 499 posts)
 
Join Date: Apr 2007
Location: College Station, TX
Posts: 57 jcarouth User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 1 Day 14 h 40 m 15 sec
Reputation Power: 8
Send a message via AIM to jcarouth Send a message via MSN to jcarouth Send a message via Yahoo to jcarouth
i mean that an the INTEGER type is always 4 bytes and always stores values between -2147483648 and 2147483647 when signed and values between 0 and 4294967295 when unsigned. the misconception is that specifying a number n, as in INT(n), will increase/decrease the range of possible values by changing the number of digits to n. however, this is not the case.

here is a quote from the manual
Quote:
Originally Posted by tfm
Another extension is supported by MySQL for optionally specifying the display width of integer data types in parentheses following the base keyword for the type (for example, INT(4)). This optional display width is used to display integer values having a width less than the width specified for the column by left-padding them with spaces.

The display width does not constrain the range of values that can be stored in the column, nor the number of digits that are displayed for values having a width exceeding that specified for the column. For example, a column specified as SMALLINT(3) has the usual SMALLINT range of -32768 to 32767, and values outside the range allowed by three characters are displayed using more than three characters.

Reply With Quote
Old October 23rd, 2007, 12:35 AM
macgyver07 macgyver07 is offline
Registered User
Codewalkers Newbie (0 - 499 posts)
 
Join Date: Oct 2007
Posts: 9 macgyver07 User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 1 h 12 m 18 sec
Reputation Power: 0
Quote:
Originally Posted by jcarouth
i mean that an the INTEGER type is always 4 bytes and always stores values between -2147483648 and 2147483647 when signed and values between 0 and 4294967295 when unsigned. the misconception is that specifying a number n, as in INT(n), will increase/decrease the range of possible values by changing the number of digits to n. however, this is not the case.

here is a quote from the manual


Ahh okay. Thank you.

Reply With Quote
Reply

Viewing: Codewalkers ForumsOther TechnologiesDatabase Help > MySQL 4.1 - When to use varchar/integer/text?


Developer Shed Advertisers and Affiliates


Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Rate This Thread:


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox
Forum Jump


Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
  
 


Powered by: vBulletin Version 3.0.5
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.

© 2003-2014 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap