• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
TEXT vs Varchar : need your advice.

#1
[eluser]FutureKing[/eluser]
I am creating a CMS which will handle articles/images/videos and more...
There are two important fields in it "Cont_text" and "Cont_type".
Cont_type will determine how to handle the content of Cont_text.
If Cont_type is image then Cont_text will be handled as filename of image
If Cont_type is article then Cont_text will be handled as html text.

The confusion/problem is, The type of Cont_text is "TEXT". Should I use "TEXT" to handle small files names as well as big articles. Or should I create separate column for storing small data like filename.

Full detail is given in the following file. Please check:
http://www.easy-share.com/1907348329/cms.doc

Please reply ASAP so that I can start building my website.

#2
[eluser]Johan André[/eluser]
I usually use varchar if:

* The content is less than 255 characters

* If I need to do automatic case-insensitive compares ('My article' = 'MY ARTICLE' = 'my article')

All my filereferences is of type varchar. Most of my fields is too (names, slugs, tags, phonenumbers, email-adresses, zipcodes, citynames, adresses, usernames, passwords).

#3
[eluser]FutureKing[/eluser]
[quote author="Johan André" date="1250354023"]I usually use varchar if:

* The content is less than 255 characters

* If I need to do automatic case-insensitive compares ('My article' = 'MY ARTICLE' = 'my article')

All my filereferences is of type varchar. Most of my fields is too (names, slugs, tags, phonenumbers, email-adresses, zipcodes, citynames, adresses, usernames, passwords).[/quote]
Thanks! But the question is...
Cont_text will be of variable length. It can become greater than 255 character in many cases and in many cases it can become less than 255 characters.
Is there any performance issue? If I use TEXT for less than 255 characters

#4
[eluser]John_Betong[/eluser]
 
Nicked from my Reference Manual:
Quote:TEXT and BLOB columns are implemented differently in the NDB Cluster storage engine, wherein each record in a TEXT column is made up of two separate parts. One of these is of fixed size (256 bytes), and is actually stored in the original table. The other consists of any data in excess of 256 bytes, which is stored in a hidden table. The records in this second table are always 2,000 bytes long. This means that the size of a TEXT column is 256 if size <= 256 (where size represents the size of the record); otherwise, the size is 256 + size + (2000 - (size - 256) % 2000).
&nbsp;
It looks to me as though if you decide to use TEXT for storing links then it will occupy a fixed allocated space of 256 bytes. It will be easier to store unknown data sizes to the TEXT field.
&nbsp;
I think it there will be no performance issues only storage size issues. The database is usually zipped when transferring and a bunch of unallocated space zips to a very small size.
&nbsp;
I reckon just go ahead and build your website because the field types can always be changed at a later date.
&nbsp;
&nbsp;
&nbsp;

#5
[eluser]jpi[/eluser]
I believe you can't set a varchar with a length greater than 255.

From mysql doc :
Quote:Before MySQL 5.0.3, a CHAR or VARCHAR column with a length specification greater than 255 is converted to the smallest TEXT type that can hold values of the given length

Quote:As of MySQL 5.0.3, a CHAR or BINARY column with a length specification greater than 255 is not silently converted. Instead, an error occurs.

#6
[eluser]FutureKing[/eluser]
[quote author="John_Betong" date="1250362516"]&nbsp;
Nicked from my Reference Manual:
Quote:TEXT and BLOB columns are implemented differently in the NDB Cluster storage engine, wherein each record in a TEXT column is made up of two separate parts. One of these is of fixed size (256 bytes), and is actually stored in the original table. The other consists of any data in excess of 256 bytes, which is stored in a hidden table. The records in this second table are always 2,000 bytes long. This means that the size of a TEXT column is 256 if size <= 256 (where size represents the size of the record); otherwise, the size is 256 + size + (2000 - (size - 256) % 2000).
&nbsp;
It looks to me as though if you decide to use TEXT for storing links then it will occupy a fixed allocated space of 256 bytes. It will be easier to store unknown data sizes to the TEXT field.
&nbsp;
I think it there will be no performance issues only storage size issues. The database is usually zipped when transferring and a bunch of unallocated space zips to a very small size.
&nbsp;
I reckon just go ahead and build your website because the field types can always be changed at a later date.
&nbsp;
&nbsp;
&nbsp;[/quote]

Thank you! I will use TEXT as field type.


Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2020 MyBB Group.