Apologies if this sounds like a unique question, can use a viewpoint:
I have got a messages table in MySQL which records messages between customers. Aside from the normal ids and message types (all integer types) I have to save the particular message text as either VARCHAR or TEXT. I am setting a front-finish limit of 3000 figures meaning the messages would not be placed in to the db as more than this.
It is possible to rationale for choosing either VARCHAR(3000) or TEXT? There is something about just writing VARCHAR(3000) that feels somewhat counter-intuitive. I have experienced other similar posts on Stack Overflow but could be perfect for get sights specific to this kind of common message storing.
Any help could be appreciated. Thanks.
TEXT and BLOB is saved from the table using the table just getting a pointer to the position of the actual storage.
VARCHAR is saved inline using the table. VARCHAR is faster once the dimensions are reasonable, the tradeoff which could be faster is dependent upon your computer data as well as your hardware, you would benchmark a realworld senerio together with your data.
Simply to clarify the very best practice:
1) Text format messages should more often than not be saved as TEXT (they finish up being randomly lengthy)
2) String characteristics ought to be saved as VARCHAR (the destination user title, the topic, etc...).
I realize that you have a front-end limit, that is great until it is not. *grin* The secret would be to think about the DB separate in the programs that connect with it. Simply because one application puts a restriction around the data, does not imply that the information is inherently limited.
What exactly is it concerning the messages themselves that forces these to not be more then 3000 figures? Whether it's just an arbitrary application constraint (say, for any text box or something like that), make use of a TEXT area in the data layer.
Disclaimer: I am not really a MySQL expert ... but this really is my knowledge of the problems.
I believe TEXT is saved outdoors the mysql row, as i think VARCHAR is saved included in the row. There's a maximum row length for mysql rows .. to help you limit just how much other data you are able to store consecutively using the VARCHAR.
Also because of VARCHAR developing area of the row, I suspect that queries searching at this area is going to be slightly faster than individuals utilizing a TEXT chunk.