Differences between MySQL character data types

By Steve Blass, Network World, 01/25/07

 Start a discussion    Print article

 Comments (1)    Print article

We have an XML schema defining documents that contain paragraphs of text collected from Web-based survey response forms, and we want to store them in a MySQL database. MySQL provides three data types for text storage elements: CHAR, VARCHAR and TEXT. Which is easiest and fastest to work with?

The differences in processing efficiency between MySQL's three character storage types should not have a big impact on processing times unless your data sets are extremely large.

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.

Using all CHAR fields for text data technically is the fastest choice, but CHAR data items have a maximum length of 255 characters. The latest versions of MySQL allow VARCHAR items to be up to 65,535 characters long, which is the maximum length of a row.

TEXT is appealing because, unlike with VARCHAR, you can put multiple full-size TEXT items in one row. To enable full-text indexing for the data, you will need to use the MyISAM table type for your database regardless of which variable types you choose for your data fields.

Featured Whitepapers

LinuxCast Listen to the latest Linux news.

About Us | Contact Us | Advertising Info | Terms of Service/Privacy

Copyright, 2006-2007 Network World, Inc. All rights reserved.