What are real performance implications of using text instead of varchar types in MySQL?

Question

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.

Answer 1

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).

Answer 2

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.