Optimizing MySQL and MariaDB for TEXT: A Guide

Tags: mysql, guide
Published: 2021-09-29 00:04:25

## Introduction
​
If you frequently find yourself working with relational database instances, and especially MariaDB or MySQL, you probably already know the nuances of a couple of data types offered by MySQL. Some of the data types provided by MySQL are suited for numbers; others fit variable-length values, of which some are also good fits for text-based values. 
​
## What Data Types are Available in MySQL?
​
Before explaining how to tune MySQL instances for specific (in this case, `TEXT`) data types, we must go over some of the data types offered by MySQL to make you understand how everything works in the first place. When it comes to data types, MySQL offers a few categories to choose from:
​

1. String data types – such data types might be helpful when the bulk of our data consists of strings or variable character values (both strings and numbers.)
2. Numeric data types – such data types might prove to be worth their salt if the data we are working on mainly consists of numbers.
3. Date data types – such data types, as the name already suggests, store dates: they might be helpful if a need to store years, months, days, etc., arises.
4. Spatial (geographic) data types – such data types can be useful to store polygon, geometric values, or the like.
5. The `JSON` datatype – such data type can be helpful when storing JSON-formatted text.
​

For this blog post, we are interested in string values—specifically, `TEXT` values. `TEXT` values differ from all of the rest even in the string space; while there are various string types available ( `CHAR`, `VARCHAR`, `BINARY`, and `BLOB` being some of them), we have chosen `TEXT` because it’s different; it differs from, for example, `VARCHAR`, because for `VARCHAR`, we can set a variable maximum character size (that’s why it’s called <code>**VAR**CHAR</code>) and the maximum character size must be between 1 and 65,535, but with `TEXT` datatype, a fixed maximum length of 65,535 characters is already set. Bear in mind that `TEXT`-based columns, generally, cannot be a part of indexes too: we cannot index `TEXT`-based columns as quickly as `VARCHAR` types.
​

## Limitations of TEXT-based Values
​
By now, you should have a basic understanding of the capabilities of `TEXT`-based workloads. Here are some nuances of the `TEXT` string data type in MySQL:

|`TEXT`-based Limitation | Explanation |
|--------------------------|--------------|
|No default values | `TEXT`-based columns cannot have preset `DEFAULT` values that `VARCHAR` columns can have. |
|Indexes | Contrary to what some people might believe, you can use indexes with `TEXT`-based columns in MySQL. However, when indexes on such columns are used, an index prefix length must be specified. |
|Data Storage Requirements | `TEXT`-based values take up 2+ stored string length bytes of disk space. |
|Variations | `TEXT` also has multiple variations. Available options include `TINYTEXT`, `SMALLTEXT`, `MEDIUMTEXT`, and `LONGTEXT`.| 
|Variation Limitations | <li> `TINYTEXT` can store values up to 255 bytes in size. 1 character = 1 byte. Maximum size – 255 bytes.</li><li>`TEXT` supports up to 65,535 characters. Maximum size – 64kB.</li><li>`MEDIUMTEXT` supports up to 16,777,215 characters. Maximum size – 16MB.</li><li>`LONGTEXT` supports up to 4,294,967,295 characters, maximum size – 4GB. All variations are very similar – the only difference is the amount of data that can be stored.</li> |

​
Other than that, in general, data types derived off of the `TEXT` data type can be beneficial if we find ourselves storing longer text values: say, pieces of code or the like. `VARCHAR` might be a fit if we are storing usernames, email addresses, or hashed and salted passwords (salts prevent hashes from being cracked as fast, so please consider *both* hashing and salting your passwords – [data breaches are rampant](https://breachdirectory.com)). `TEXT` data types might be just what we need if we want to store text that looks more like a small story but does not consist only of a few characters.
​
## When to Use TEXT?
​
By now, you should have some understanding as to what kind of a beast you’re dealing with; indeed, `TEXT`-based values are another data type breed altogether. What makes them so different is that they are specifically suited for storing vast amounts of text. Text can not necessarily mean “text” in a traditional sense; you can store numbers as well, but remember that `TEXT` is not `VARCHAR` – once you elect to choose `TEXT`, be ready for particular challenges. Some are outlined above (you cannot add an index on the entire column, etc.), while others are lesser-known but still important to keep in mind. For example, `TEXT` data types would generally only be helpful if our database instances have a lot, and I mean, **a lot** of storage space since `MEDIUMTEXT` can store strings up to 16MB in size, and if we elect to use `LONGTEXT`, the limit is increased to 4GB.
​

**In general, though, any variant of the `TEXT` datatype is useful if our application needs to store longer text strings and where we might find that `VARCHAR` just does not cut it.**
​
## SQL Clients?
​
Data types are, of course, important, but what’s just as important is choosing the correct SQL client to perform your tasks. SQL clients like [Arctype](https://arctype.com) are fast, and they provide you with the data types your MySQL instance is using, as well as some insight into how to optimize [your schema](__GHOST_URL__/mysql-schema/) further :
![](https://arctype.com/blog/content/images/2021/08/image-12.png)

![](https://arctype.com/blog/content/images/size/w1000/2021/08/image-13.png)

Anyway, we hope that this blog post has provided you with some insight into the `TEXT` data type world of MySQL. So, if you enjoyed reading, make sure to [explore more content](https://arctype.com/blog), and see you in the next one.

Lukas is an ethical hacker, a MySQL database administrator, and a frequent conference speaker. Since 2014 Lukas has found and responsibly disclosed security flaws in some of the most visited websites in Lithuania and abroad including advertising, gift-buying, gaming, hosting websites as well as some websites of government institutions. Lukas runs one of the biggest & fastest data breach search engines in the world - BreachDirectory.com and frequently blogs in multiple places educating people about information security and other topics. He also runs his own blog over at lukasvileikis.com