MySQL: Large VARCHAR vs. TEXT?

mysql text messages varchar
Tom picture Tom · Jan 7, 2010 · Viewed 475.5k times · Source

I've got a messages table in MySQL which records messages between users. Apart from the typical ids and message types (all integer types) I need to save the actual message text as either VARCHAR or TEXT. I'm setting a front-end limit of 3000 characters which means the messages would never be inserted into the db as longer than this.

Is there a rationale for going with either VARCHAR(3000) or TEXT? There's something about just writing VARCHAR(3000) that feels somewhat counter-intuitive. I've been through other similar posts on Stack Overflow but would be good to get views specific to this type of common message storing.

Answer

MindStalker picture MindStalker · Jan 7, 2010

  • TEXT and BLOB may by stored off the table with the table just having a pointer to the location of the actual storage. Where it is stored depends on lots of things like data size, columns size, row_format, and MySQL version.

  • VARCHAR is stored inline with the table. VARCHAR is faster when the size is reasonable, the tradeoff of which would be faster depends upon your data and your hardware, you'd want to benchmark a real-world scenario with your data.

Related questions

Difference between VARCHAR and TEXT in MySQL

When we create a table in MySQL with a VARCHAR column, we have to set the length for it. But for TEXT type we don't have to provide the length. What are the differences between VARCHAR and TEXT?

Read More
What are the differences between the BLOB and TEXT datatypes in MySQL?

What is blob and what is text? What are the differences? When do I need to use blob and when do I need text as data type? Because for blob and text, there are mediumblob == mediumtext, smallblob == small text. Do …

Read More
BLOB/TEXT column 'bestilling' used in key specification without a key length

I am trying to make a order system, but I am stuck right now. In the mysql tabel right now, I am using varchar(255) in a column named "bestillinger", but it can only store 255 chars. So I searched a bit, …

Read More