Differences between MySQL character data types

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.

To continue reading, register here and become an Insider. You'll get free access to premium content from CIO, Computerworld, CSO, InfoWorld, and Network World. See more Insider content or sign in.

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.

Read more about small business networking in Network World's Small Business Networking section.