Long Text or Varchar

[andydeans]

Hi,

I am setting up a notes table, and wondered what the best type would be?

Currently i have set it to Varchar with length 500

Would that take up more space than say a Lontext type in the database?

Thanks

[karol33]

no doubt - text

[tesuji]

Well, no doubt :-/

One should consider some drawbacks of datatype text over varchar, for example attributes of datatype text as well as blobs will be stored separately from all other attributes (they are not stored within contiguous pages!), also if text attributes appear together with other attributes in a select statement, the resultset won't never be hold in main memory completely but be stored on disk. There are further limitations if attributes of text datatype be compared, sorted, manipulated with string functions etc.

So, if one doesn't need to handle more then 64k characters, why should he be burden with such unfavorable stuff? Therefore, varchar datatype is the far more appropriate datatype for dealing with such short notes, there is really no doubt whatsoever ;)

-- tesu

[karol33]

Standard
VARCHAR is actually part of the ISO SQL:2003 standard; The TEXT data types, including TINYTEXT, are non-standard.
Storage
TEXT data types are stored as separate objects from the tables and result sets that contain them. This storage is transparent — there is no difference in how a query involving a TEXT field is written versus one involving a VARCHAR field. Since TEXT is not stored as part of a row, retrieval of TEXT fields requires extra I/O overhead.
Maximum VARCHAR length
The maximum row length of a VARCHAR is restricted by the maximum row length of a table. This is 65,535 bytes for most storage engines (NDB has a different maximum row value). Theoretically the maximum length of a VARCHAR is 65,536 bytes. Overhead further limits the actual maximum size of a VARCHAR.
Storing the length of a VARCHAR field takes 1 byte if the VARCHAR field has a maximum length of 0-255 bytes; if it is greater than 255 bytes, the overhead to store the length is 2 bytes. If the VARCHAR field allows NULL values, that adds additional overhead — every table uses 1 byte of overhead for each set of 8 fields that allow NULL values. If the VARCHAR is the only row in the table, and does not allow NULL values, the maximum length allowed for VARCHAR is 65,532 bytes.
Keep in mind that that the number in VARCHAR(x) represents number of characters, not number of bytes. Therefore, you may have difficulties trying to define a table with only VARCHAR(65532) if the character set uses multi-byte characters, such as UTF-8.
If you attempt to define a VARCHAR value that is longer than allowed, you will run into an error such as 1118 or 1074:
ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. You have to change some columns to TEXT or BLOBs.
ERROR 1074 (42000): Column length too big for column 'col_name' (max=[max number here]); use BLOB or TEXT instead
Maximum TEXT length
The maximum size of a TEXT data type depends on which type of TEXT data type is being used. Because they are stored as objects, the only row overhead in the table object is a pointer (8 or 16 bytes). Here is a list of the maximum TEXT length, and the overhead (in the TEXT object):
TINYTEXT – up to 255 bytes, 1 byte overhead
TEXT – up to 64 Kb, 2 bytes overhead
MEDIUMTEXT – up to 16 Mb, 3 bytes overhead
LONGTEXT – up to 4 Gb, 4 bytes overhead
DEFAULT values
MySQL does not allow TEXT data types to have a default value other than NULL. VARCHAR fields are allowed to be created with a DEFAULT
value.
so for names VARCHAR for longer text TEXT

[andydeans]

Thnks for your both lengthy replies, i do not fully understand the I/O overheads etc you are talking about.

My situation is i have a site on a shared hosting web server, the users may add notes everyday so probably betwen 20-30 notes a day added.

i set the length to 500, woudl this have problems that you have mentioned about being a limit of 65,535 bytes ??

I am not too sure so be grateful if you could guide me as i do not want to run into problems later on.

The notes table has 5 columns

NoteID (Int PK AI)
NoteUserID (Int FK Users.UserID)
NoteClientID (Int FK Clients.ClientID)
Note (Varchar 500)
DateCreated (Current Timestamp)

Appreciate your help.

Many thanks

[tesuji]

Hi
Karol has already enumerated all most important advantages of varchar over text DT, so it seems rather impossible to abandon varchar without good cause.

As for your five-column table: What do you further want to do with PK NoteID? Isn't there a true natural primary key given by triple (NoteUserID, NoteClientID, DateCreated). One should also taken into account that (NoteUserID, NoteClientID) obviously defines a many-to-many relationship between entities USERS and CLIENT.

-- tesu

[andydeans]

Hi,

I will be displaying all notes by user for their client.

so should i not bother with noteid?

will varchar 500 be a problem with limit of 65,535 bytes ??

Not really understanding that part. Also if i have a database limit of 500mb and my size so far with only 35 records in it is 596.8 KiB

how many records should i be able to hold? in theory? sorry for dumb question not really understanding that part of it.

many thanks

[griswolf]

noteId might be nice to have in the future as a foreign key. Right now not needed as pointed out by tesuji.

Varchar(500) will take a little over the number of bytes needed to store the datum, with a maximum of a little over 500 bytes as karol33 mentions (he appears to have got the data from here: http://www.pythian.com/news/7129/text-vs-varchar/). So for your case there will be no problem at all: You could have varchar(65000) if you needed it, though I would not recommend that size.

If your table scales linearly (it will, almost, nearly, but not from a zero base), then you could store about 28 thousand items. (I think you are concerned about your ISP's limit?). Do this math:

ohead = sizeof(database with 1 record)
per_record = (sizeof(database with 51 records) - ohead)/50
max = 5000000/per_record

... but remember that you may want some other tables too

[andydeans]

Thanks griswolf. How would i know if it was stored linearly ? is there other types of how it stores that i should know about? never seen that before.

thanks again so you reckon varchar 500 should be ok for my notes page?

thank you

[griswolf]

By "scales linearly" I mean that the size is (almost, nearly) a linear function of the number of rows: size = const + ratio * row_count . Doing the calculation I mentioned above would allow you to find out approximately how many entries you would get in 500Mb of storage. Nothing I said was about the storage technique itself, which I could guess about, but don't know.

Only you can decide if 500 characters is the right size. It is kind of short if you are storing things like we see here at Daniweb, but longer than a tweet, just to give you two examples.

[andydeans]

thanks again griswolf.

is 500mb standard good space for a business database? provided by my hosting company.

thank you again

[xuqi]

TEXT

[andydeans]

hi xuqi, can you expand on why text over varchar?

thanks

[griswolf]

thanks again griswolf.

is 500mb standard good space for a business database? provided by my hosting company.

thank you again

It is certainly one of the usual amounts. Some hosting companies give more, a few give less; and of course if you need more almost every ISP will sell you more.

[andydeans]

thansk griswolf