Partitioning MySQL: Why, When, How?

What are Partitions?

Partitioning, simply put, is how a database splits data into separate tables but still treats the data as one table by the SQL layer. Partitions, just like indexes, are a frequent guest in the database world no matter what kind of a database you use — its concept is the same no matter if you use Oracle, MongoDB, MariaDB, or MySQL.

Partitions divide a table into segments making it easier to manage and query data. In addition, partitioning can be useful when a need to store data in multiple different locations arises. By partitioning your data, you can define that you want certain pieces of data to be stored separately from the rest of the data, thus potentially overcoming some storage problems that might torment your applications or databases.

When Should You Consider Partitioning Your Tables?

You might want to consider partitioning the tables inside of your databases if:

• You want to improve query performance, and the indexes that you use just don't cut it.
• You want to store bits and pieces of your data in different locations for storage reasons.
• You think that you might want to backup and restore specific pieces of data.
• You have trouble scaling your databases.

At this point, it's worth mentioning that just like indexes, partitions have their own upsides and downsides. Some of their main upsides are discussed above. However, there are quite a few limitations to such an approach:

1. One table can have a maximum of 1,024 partitions if we use an older version of MySQL (< 5.6). Newer versions of MySQL (5.7 and up) can support up to 8,192 partitions.
2. If we use MySQL 5.1, the partition expression must either be an integer or an expression that returns an integer.
3. There are some partitioning limitations related to certain storage engines in MySQL:
   • FEDERATED and CSV storage engines do not support partitioning at all. In addition, tables using the MERGE storage engine cannot be partitioned either.
   • Partitioned InnoDB tables do not support foreign keys.
   • Out of all storage engines that can be used, only MyISAM supports using multiple disks for subpartitions.
   • The NDB storage engine only supports partitioning by KEY.
   • All partitions of a partitioned table must use the same storage engine. The table must use the same storage engine as the partitions, too-no exceptions here either.
4. If we want to use functions with partitions, we must keep in mind that MySQL has a whitelist of functions that are allowed to execute — other functions are not supported, and thus, they will not work.

If you are not scared away by these issues and still want to use partitioning, partitioning might be a good idea — partitioning might not be such a good idea in other scenarios.

Should You Use Partitions?

We have touched upon the reasons when you should consider partitioning but did not yet fully answer the question of why you should consider partitioning your tables in the first place. Should you even do that?

To start with, partitioning might provide numerous benefits if SELECT queries are in use. They sometimes can be used together with indexes to improve query performance even further. However, to fully understand should you use partitions or not, you have to keep the following things in mind:

• Partitions, generally, are only useful when you have a lot of data. If you only have a couple of million rows inside of your table, partitioning might not be a great idea, but you might benefit from partitioning if you deal with, say, more than 100 or 200 million rows.
• There are multiple types of partitions, each of which might be useful for different purposes — you can choose from partitioning by RANGE, KEY, LIST, or COLUMNS, you can also partition tables by HASH values: we won't go into all of the nitty-gritty detail about partitioning types here, but keep in mind that partitioning by RANGE assigns rows to partitions that are in a certain range, partitioning by COLUMNS allows you to use multiple columns in partitioning keys, partitioning by HASH can help ensure that data is distributed evenly across multiple partitions, etc.
• If you have NULL values inside of your columns ( NULL values can be beneficial if you are running queries that check whether the value of a column is NULL or not), be aware that MySQL does not disallow NULL as the value of a partitioning expression and that MySQL might treat NULL values in interesting ways — NULL values are treated differently in different partitioning types (in some cases, MySQL can even treat columns with NULL values as special)
• Bear in mind that if you are using partitions and want to run partition maintenance operations, doing so might be a painful process because some of the partition maintenance operations work similarly to ALTER TABLE queries in MySQL: they copy data back-and-forth, which can take a lot of time, especially if you have a lot of data.
• If you elect to use partitions, choose your partition key wisely — the more partitions your query accesses, the slower it will be.

Make use of the advice above, and you should be able to dive into the MySQL partitioning world with confidence.

Arctype to the Rescue

Partitions, as already noted, are frequent friends in the SQL query optimization space. However, when using partitions, you must make sure that MySQL actually uses them by running queries against your engine. For example, in MySQL 5.1.5, MySQL has added a feature that allows developers and DBAs to determine which partitions of a partitioned table are actually used by MySQL — use EXPLAIN PARTITIONS instead of EXPLAIN and you're good to go! Such types of queries can easily be run via the terminal or even via phpMyAdmin.

However, if you also want to run other types of queries against your MySQL instance, you might need a SQL client offering more features — here's where Arctype can assist. Arctype can allow you to run, design, and even save your SQL queries if you so desire. For example, here's how the UI of Arctype looks like:

Want to share your queries with your teammates? Not an issue! Just click the Share button on the top left-hand side. You will then be able to share the query you just crafted via email or, if you want to, get a unique link:

Arctype can be one of your trusted partners in the MySQL space, too — if you find yourself searching for a SQL client, be sure to try it out!

Summary

Partitions in MySQL have their own use cases. They also have their own distinct advantages and disadvantages. Before deciding to use partitions inside your MySQL instances, be aware of the upsides and pitfalls outlined in this article and choose wisely — one wrong step in this space can be costly in the future. However, do not worry too much — a SQL editor like Arctype can quickly and easily put you on the right track towards accomplishing your performance goals.