5

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.

Improve this question
3
  • you will not use the column in WHERE clauses or SELECT substrings either, right? Commented Jan 7, 2010 at 0:48
  • @Thilo: Right, I won't use it in WHERE. Commented Jan 7, 2010 at 0:49
  • 1
    Text is fine provided you don't include the column as an output column in your result sets. I.e. Only ever return the text column in queries that return a single row. NOTE: This should be the natural thing to do in any case because such a 'large' column is only really useful when presented to the user for a specific row. However, some developers make the mistake of excessively using SELECT * FROM .... Commented Jan 7, 2010 at 8:12

2 Answers 2

Reset to default
5

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

Improve this answer
Sign up to request clarification or add additional context in comments.

8 Comments

Well, but in that article the TEXT/VARCHAR column is used as the primary key. The OP said he would not be indexing the column.
Thilo - you're correct, I just wanted to address the more general issue of "performance implications of text vs varchar". Someone else reading might find it useful.
Yeah, but I think the measurement was influenced by indexing. I won't make text column a primary key.
What exactly does the temporary table mean? Is it used often or rarely?
Thanks, I found these comments during my prior investigation as well. While interesting, it is difficult to guess from these what is the real impact for relatively small (kilos not megs) entries.
|
1

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.

Improve this answer

1 Comment

I think this is of interest for many database architects not necessarily tighten up with one concrete layout and architecture. This kind of "find it yourself" answers is useful neither for me nor anyone else. :)

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.