Menu
We have a very large historical table that contains a column with at most 500 UTF8 characters, and the disk space grows really fast!
We're having at least 2 million rows a day... and we were wondering which would do a better job (mostly in storage but in performance as well)? TEXT or VARCHAR(512)?
677
In most circumstances, VARCHAR provides better performance, it's more flexible, and can be fully indexed. If you need to store longer strings, use MEDIUMTEXT or LONGTEXT, but be aware that very large amounts of data can be stored in columns of these types.
CHAR items, which are fixed length, are the fastest to store and retrieve but can waste storage space. VARCHAR, a variable-length string, can be slower to store and retrieve but does not waste storage space. TEXT is a character BLOB that requires more storage space and I/O than the other two.
There is no a big problem to change varchar to text because text supports more data length than varchar , but if the field has a index it must be drop and create new index with prefix col_name(length) (see CREATE INDEX syntax).
TEXT is the family of column type intended as high-capacity character storage. The actual TEXT column type is of four types-TINYTEXT, TEXT, MEDIUMTEXT and LONGTEXT. The four TEXT types are very similar to each other; the only difference is the maximum amount of data each can store.
VARCHAR is probably preferable in your case from both the storage and performance perspective. View this oft-reposted article.
100
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
