Which data type saves more space TINYTEXT or VARCHAR for variable data length in MySQL?

Question

I need to store a data into MySQL. Its length is not fixed, it could be 255 or 2 characters long. Should I use TINYTEXT or VARCHAR in order to save space (speed is irrelevant)?

Answer 1

When using VARCHAR, you need to specify maximum number of characters that will be stored in that column. So, if you declare a column to be VARCHAR(255), it means that you can store text with up to 255 characters. The important thing here is that if you insert two characters, only those two characters will be stored, i.e. allocated space will be 2 not 255.

TINYTEXT is one of four TEXT types. They are very similar to VARCHAR, but there are few differences (this depends on MySQL version you are using though). But for version 5.5, there are some limitations when it comes to TEXT types. First one is that you have to specify an index prefix length for indexes on TEXT. And the other one is that TEXT columns can't have default values.

In general, TEXT should be used for (extremely) long values. If you will be using string that will have up to 255 characters, then you should use VARCHAR.

Hope that this helps.

Answer 2

As for data storage space, VARCHAR(255) and TINYTEXT are equivalent:

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

TINYTEXT: L + 1 bytes, where L < 2⁸.

Source: MySQL Reference Manual: Data Storage Requirements.

---

Storage space being equal, you may want to check out the following Stack Overflow posts for further reading on when you should use one or the other:

• What’s the difference between VARCHAR(255) and TINYTEXT string types in MySQL?
• varchar(255) v tinyblob v tinytext