varchar and text data types in MySQL

Do you know the difference between the varchar and text types in MySQL? Up until MySQL 5.0.3, the varchar data type could only hold up to 255 characters, but from 5.0.3 onwards, it can store up to 65,535, which means that it can store data that previously would have been held in text fields. This is important because text is a fundamentally different data type to varchar. In fact is is similar to the blob data type, and has a couple of disadvantages:

  1. You can’t make a complete index on a text column, so MySQL will often have to read from the table.
  2. MySQL can’t make temporary tables in memory for tables that use text columns, it has to create temp tables on disc for them.

So most of the time you’ll probably be better off using varchar columns rather than text. For more info, see:

varchar length:
http://dev.mysql.com/doc/refman/5.0/en/char.html

Indexes:
http://forums.mysql.com/read.php?24,105964,105984#msg-105984

How MySQL uses temporary tables:
http://dev.mysql.com/doc/refman/5.0/en/internal-temporary-tables.html

This entry was posted in MySQL and tagged . Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *

503,487 Spambots Blocked by Simple Comments

HTML tags are not allowed.