CodeIgniter Forums
TEXT vs Varchar : need your advice. - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forum-20.html)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forum-23.html)
+--- Thread: TEXT vs Varchar : need your advice. (/thread-21602.html)



TEXT vs Varchar : need your advice. - El Forum - 08-15-2009

[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.


TEXT vs Varchar : need your advice. - El Forum - 08-15-2009

[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).


TEXT vs Varchar : need your advice. - El Forum - 08-15-2009

[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


TEXT vs Varchar : need your advice. - El Forum - 08-15-2009

[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;


TEXT vs Varchar : need your advice. - El Forum - 08-15-2009

[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.



TEXT vs Varchar : need your advice. - El Forum - 08-15-2009

[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.