Menu
Is there/has somebody any comparison, personal experience, or guideline when to use the text type instead of a large varchar in MySQL?
While most of the entries in my database will be less than 1000 characters, some might take up to 4000 characters or more. What is the limiting length of varchar which makes text a better variant?
I do not need to index those fields.
909
Some Differences Between VARCHAR and TEXT The VAR in VARCHAR means that you can set the max size to anything between 1 and 65,535. TEXT fields have a fixed max size of 65,535 characters. A VARCHAR can be part of an index whereas a TEXT field requires you to specify a prefix length, which can be part of an index.
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.
Because of the fixed field lengths, data is pulled straight from the column without doing any data manipulation and index lookups against varchar are slower than that of char fields. CHAR is better than VARCHAR performance wise, however, it takes unnecessary memory space when the data does not have a fixed-length.
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.
I don't have personal experience, but this guy does:
VARCHAR vs. TEXT - some performance numbers
Quick answer: varchar was a good bit faster.
Edit - no, it wasn't. He was indexing them differently - he had a full index on the varchar (255 chars) but a 255-char prefix index on the text. When he removed that, they performed more or less the same.
Later in the thread is this interesting tidbit:
When a tmp table is needed for a SELECT, the first choice is to use MEMORY, which will be RAM-only, hence probably noticeably faster. (Second choice is MyISAM.) However, TEXT and BLOB are not allowed in MEMORY, so it can't use it. (There are other reasons why it might skip MEMORY.)
Edit 2 - some more relevant info, this time comparing the way different indices deal with the various types.
MyISAM puts TEXT and BLOB 'inline'. If you are searching a table (range scan / table scan), you are 'stepping over those cow paddies' -- costly for disk I/O. That is, the existence of the inline blob hurts performance in this case.
InnoDB puts only 767 bytes of a TEXT or BLOB inline, the rest goes into some other block. This is a compromise that sometimes helps, sometimes hurts performance.
Something else (Maria? Falcon? InnoDB plugin?) puts TEXTs and BLOBs entirely elsewhere. This would make a noticeable difference in performance when compared to VARCHAR. Sometimes TEXT would be faster (eg, range scan that does not need the blob); sometimes the VARCHAR would be faster (eg, if you need to look at it and/or return it).
109
Of course the best way to know is to run some tests yourself with your real dataset, or at least a simulated equivalent. Just write some scripts to populate the data and run your selects. Test with varchar at different sizes, then text, and measure both the timing and overall system utilization (cpu/load, memory, disk i/o).
If you are going to have enough load that this will matter then you ought to have automated tests anyway.
40
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
