In this article, we will discover the difference between character types of MySQL, namely the CHAR, VARCHAR, and TEXT.
Differences in speed between these three MySQL data types will be very difficult to detect until our database grows to quite large volumes.
So which one is the easiest to work with and what type of data provides faster work?
What is CHAR storage type
In a column of type CHAR, the field length is constant and is specified when creating the table.
This length can take any value between 1 and 255 (as for MySQL version 3.23, the length of the CHAR column in it can be from 0 to 255).
Values of type CHAR during storage are padded on the right with spaces up to the specified length. These trailing spaces are removed when retrieving stored values.
If the value specified in the CHAR column exceeds the maximum permissible length of the column, then this value is truncated accordingly.
Data of the CHAR type, which has a fixed length, is the fastest to save and retrieve information but can consume excessive disk space.
What is VARCHAR storage type
Values in VARCHAR columns are variable-length rows.
Unlike the CHAR data type, we can specify the length of the VARCHAR data type. VARCHAR length in MySQL vary between 1 and 255. However, unlike CHAR, when storing values of type VARCHAR, only the number of characters needed plus one byte is required.
Stored values are not supplemented with spaces, on the contrary, trailing spaces are deleted during storage (the described process for removing spaces differs from the ANSI SQL specification).
If the value specified in the VARCHAR column exceeds the maximum permissible length of the column, then this value is truncated accordingly.
VARCHAR, a string of varying lengths, runs slower than CHARs but does not consume excessive disk space.
What is TEXT storage type
This type allows us to save texts that can take from 1 byte to 4 gigabytes. This type is often used to save articles or long descriptions of something. Moreover, we do not need to specify the size of the content when we decide to use this type.
This type is the slowest as it uses the disk-based temporary tables rather than the RAM.
Type TEXT is a variable of type BLOB – it takes up more space and disk I/O than the previous 2 types.
When to use?
Using the CHAR type for text data is technically the fastest way, but CHAR data has a length limit of 255 characters. The latest version of MySQL DBMS allows storing data up to 65,535 characters in a variable of type VARCHAR, this is the maximum length of this data type.
The TEXT type is more attractive from the first look because unlike the VARCHAR type, we can store multiple TEXT elements in one cell. In order to enable full-text indexing of text data, it is necessary that the table type is MyISAM since it does not pay attention to what data type is.
Instant livechat to an Expert!
Most of the time, the problem you will need to solve will be more complex than a simple method. If you want to save hours of research and frustration, try our live SQLExpert service!
Our SQLQuerychat Experts are available 24/7 to answer any questions you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.