How to Partition MySQL Tables

Horizontal and Vertical Partitioning

There are two types of database partitions: vertical and horizontal.

Vertical Partitioning

Vertical partitioning relies upon initially creating tables with fewer columns and then using additional tables to store leftover columns. Since rows are split according to their columns, vertical partitioning is also known as row splitting (opposite horizontal partitioning). As of right now, MySQL does not support vertical partitioning in its database.

Horizontal Partitioning

The partitioning logic divides the rows into multiple tables. The number of columns remains constant throughout partitions, while the number of rows can vary. My SQL currently supports horizontal partitioning. In this post, we’ll look at three different kinds of horizontal partitions in MySQL.

Range Partitioning

When implementing range partitioning, if a column value falls within the specified range for a particular partition, the row is then added to that partition.

Implementing Range Partitioning

1. Create a sample table:

  CREATE TABLE arctype.range_crypto(
  timestamp INT,
  open DOUBLE,
  close INT,
  high DOUBLE,
  low DOUBLE,
  volume DOUBLE,
  );

2. (Optional) Populate the table with sample data. In this case, download this dataset. Then, import the CSV data into a table.
3. Create the range partitions using the ALTER TABLE expression:

  ALTER TABLE arctype.range_crypto
  PARTITION BY RANGE (close) (
      partition p0 VALUES LESS THAN (10000),
      partition p1 VALUES LESS THAN (20000),
      partition p2 VALUES LESS THAN (30000),
      partition p3 VALUES LESS THAN (40000),
      partition p4 VALUES LESS THAN (50000),
      partition p5 VALUES LESS THAN (60000),
      partition p6 VALUES LESS THAN  MAXVALUE
  )

4. You can now query data from any of the partitions you have created:

  SELECT * 
  FROM arctype.range_crypto PARTITION (p3) 
  WHERE close BETWEEN 35000 and 38000;

Conversely, if you run this query:

  SELECT * 
  FROM arctype.range_crypto PARTITION (p0) 
  WHERE close BETWEEN 35000 and 38000;

then the result will come up empty. Specifying the partition shows MySQL where to look, making your queries run faster (if you have millions of rows).

Another variation of the range partitioning is the RANGE COLUMNS. It lets you specify more than one partitioning column. Let us create range partitions that hold a range of close prices over some time.

  ALTER TABLE arctype.range_crypto 
  PARTITION BY RANGE COLUMNS (timestamp, close) (
    PARTITION from_2018_10k VALUES LESS THAN (1533127192, 10000),
    PARTITION from_2019_20k VALUES LESS THAN (1564663192, 20000),
    PARTITION from_2020_30k VALUES LESS THAN (1596285592,30000),
    PARTITION from_2021_40k VALUES LESS THAN (1627821592,40000 ),
    PARTITION from_latest_highest VALUES LESS THAN (MAXVALUE, MAXVALUE)
  );

If you want to query all the rows with timestamps between 2018 and 2019 where prices closed between 13500 and 11600, you can run a query like this:

  SELECT * 
  FROM arctype.range_crypto PARTITION(from_2019_20k) 
  WHERE close BETWEEN 11600 AND 13500

List Partitioning

In list partitioning, rows are grouped on the premise that their value in the column used for partitioning is similar to a value in the list (set of discrete values) defined. To put things in perspective, when you create the partitioning list(s), MySQL checks the question, “Does a column in this row have a similar value with values in this list?” If the value is the same, MySQL adds that row to the partition for that value in your list.

Implementing List Partitioning

1. Create the sample table using:

  CREATE TABLE arctype.football(
  home_team TEXT,
  away_team TEXT,
  home_goals INT,
  away_goals INT, 
  result TEXT,
  season TEXT
  );

2. Optional — Populate the table with sample data. In this case, download this dataset.

3. You can now create the list partition using the ALTER TABLE expression.

  ALTER TABLE arctype.football
  PARTITION BY LIST (home_goals) (
  PARTITION odd VALUES IN (1,3,5,7,9),
  PARTITION even VALUES IN (0,2,4,6,8)
  );

We can now write queries using the partitions we just created:

  SELECT * FROM arctype.football PARTITION(odd) WHERE (home_goals=3);

Hash Partitioning

When defining the previous partitions, you had to state which column value, range, or column values fell into a particular partition. With hash partitioning, you must specify the number of partitions you want for the column (or expression). MySQL uses MOD(expression, number of partitions) to determine which partition any row falls in. To understand how this works, look at this example:

For the first row, the partition it belongs to based on the home_goals column is determined using MOD(1,2)=1. The partition for the third row will be selected using MOD(2,2)=0. If you have an imaginary row with 9 home_goals, MOD(9,2)=1.

Implementing Range Partitioning

You can implement range partitioning in MySQL using the same ALTER TABLE statement.

ALTER TABLE arctype.football
PARTITION BY HASH (home_goals)
PARTITIONS 2;

Conclusion

In general, partitions speed up your searches. While this is correct, the effect of partitions is not readily apparent in smaller tables. So, if your queries are executing slowly and your database table does not include millions of rows, you should consider other optimization approaches before splitting your tables.

Further Reading

1. https://en.wikipedia.org/wiki/Partition_(database)
2. https://dev.mysql.com/doc/refman/8.0/en/partitioning.html
3. https://www.w3resource.com/mysql/mysql-partition.php