MySQL CHAR vs VARCHAR: How to Choose

Choosing between the CHAR and VARCHAR data types is a fundamental decision when designing a MySQL database schema. This article provides a direct comparison of these two character string types, explaining how they store data, their performance implications, and the specific scenarios where you should choose one over the other to optimize your database efficiency and storage space.

Key Differences: Storage and Behavior

The primary difference between CHAR and VARCHAR lies in how they store data on the disk and how they handle padding.

• CHAR (Fixed Length): The CHAR data type stores fixed-length character strings. When you define a column as CHAR(M), where M represents the number of characters, MySQL allocates exactly M characters of space for every single row, regardless of the actual length of the stored string. If the inserted value is shorter than M, MySQL pads the value with spaces on the right. When retrieved, these trailing spaces are removed by default.
• VARCHAR (Variable Length): The VARCHAR data type stores variable-length character strings. When you define a column as VARCHAR(M), the M represents the maximum allowed length. MySQL only stores the actual characters you insert, plus an extra 1 or 2 bytes to record the length of the string (1 byte if the maximum length is 255 characters or fewer, and 2 bytes if it is greater). Trailing spaces are preserved when stored and retrieved.

When to Choose CHAR

Use the CHAR data type when your data has a predictable, consistent length.

• Fixed-length values: MD5 or SHA-1 hashes (always 32 or 40 characters), UUIDs (36 characters), state abbreviations (e.g., “CA”, “TX”), or ISO country codes (e.g., “USA”, “CAN”).
• Highly updated columns: If a column’s values are constantly updated, CHAR prevents row fragmentation. Because the allocated space is fixed, updating a CHAR value never changes the physical size of the row, preventing the database from having to move data around on the disk.
• Very short strings: For columns that store strings of 1 or 2 characters (like “Y” or “N” flags), CHAR is more efficient because it does not require the overhead of the 1-byte length prefix used by VARCHAR.

When to Choose VARCHAR

Use the VARCHAR data type when the length of the data fluctuates significantly from row to row.

• Variable-length values: Names, email addresses, street addresses, and description fields where the length can range from a few characters to hundreds.
• Space conservation: Storing a 10-character name in a CHAR(255) column wastes 245 bytes of storage per row. Using VARCHAR(255) instead will only use 11 bytes of storage (10 bytes for the string and 1 byte for the length prefix), significantly reducing your database’s overall disk footprint.
• Large text fields: Any text field where the maximum length is high but the average input size is relatively low.

Performance Considerations

While CHAR can be slightly faster than VARCHAR because the database engine does not need to read length prefixes to locate the end of a string, this performance difference is usually negligible in modern storage engines like InnoDB.

Instead, the main performance impact comes from memory usage. MySQL often allocates the maximum potential size in memory when creating temporary tables for queries involving VARCHAR columns. Therefore, even when using VARCHAR, you should still set the maximum length (M) to a realistic limit rather than defaulting to VARCHAR(255) for every field.