Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I want to make a small social network. For messaging body which one is more suitable? VARCHAR or TEXT? if VARCHAR how long is efficient?

share|improve this question

closed as too broad by Paul White, bluefeet, RolandoMySQLDBA, Max Vernon, ypercube Oct 3 '13 at 20:10

There are either too many possible answers, or good answers would be too long for this format. Please add details to narrow the answer set or to isolate an issue that can be answered in a few paragraphs.If this question can be reworded to fit the rules in the help center, please edit the question.

2  
Sow which DBMS are you using? Postgres? –  a_horse_with_no_name Oct 3 '13 at 10:32
2  
Depends on the concrete database you're using.... MySQL? Postgres? SQL Server? For SQL Server, Text and NText are deprecated and should not be used anymore –  marc_s Oct 3 '13 at 14:14

1 Answer 1

To completely determine column type, I might suggest to do following:

  1. Check possible data types in real world: is it string, or number, or large text block, or date?
  2. Determine which operation types you would like to perform with the column: for example, barcode has numeric format, but calculate sum or average of barcode isn't valid.

You have selection of two types: TEXT or VARCHAR. If you would like to perform FULLTEXT search, then you have to use TEXT. For other cases VARCHAR acts almost same as TEXT.

"Which of them to use?" depends on subject area and is primarily opinion-based question. If you ask me, then I might suggest to use VARCHAR (if no fulltext search is planned) up to VARCHAR(2000).


From MySQL.com:

In the following table, M represents the declared column length in characters for nonbinary string types and bytes for binary string types. L represents the actual length in bytes of a given string value.

VARCHAR(M): L + 1 bytes if column values require 0 – 255 bytes, L + 2 bytes if values may require more than 255 bytes.

TEXT: L + 2 bytes, where L < 2^16.

For small messages, storage space of which requires less than 255 bytes, VARCHAR will save some extra disk space.


I'm supposing that you use MySQL RDBMS.

share|improve this answer
    
thanks for your answer. You pointed to important issue. VARCHAR(2000) is fine however many people may only send Hi how are you message to each other. which wastes a lot of storage. I dont want to use full text search on messages. Does using TEXT slows down my database significantly? and what is the advantage of VARCHAR over TEXT? i use relational model. –  meshiv Oct 3 '13 at 8:43
    
@meshiv I've updated. –  HAL9000 Oct 3 '13 at 8:55

Not the answer you're looking for? Browse other questions tagged or ask your own question.