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?

  • 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

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.

| 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. – BlitZ Oct 3 '13 at 8:55

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