MySQL Forums
Forum List  »  Performance

Re: VARCHAR vs. TEXT - some performance numbers
Posted by: Rick James
Date: June 16, 2009 10:17PM

There are reasons for VARCHAR(255) being better than TEXT. (Your test did not find one, as was pointed out.)

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

Note: I believe the above comment applies even for TINYTEXT, which is nearly identical to VARCHAR(255).

Prefix indexes are almost always bad for performance. And they have a gotcha -- In your example (with "PRIMARY KEY (a(255))"), you are enforcing that the first 255 bytes of 'a' are distinct. (Yes, that was quite ok for your test, but is usually 'wrong' in real life.)

VARCHAR(255) can hold 255 characters.
TINYTEXT can hold 255 bytes.
Note the subtle difference, especially when running with utf8. This means that the former may hold more stuff.

Options: ReplyQuote

Written By
Re: VARCHAR vs. TEXT - some performance numbers
June 16, 2009 10:17PM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.