What is partitioning in MySQL?

Partitioning in MySQL is used to split or partition the rows of a table into separate tables in different locations, but still, it is treated as a single table. Partitioning is a technique used to divide a large table into smaller, more manageable segments called partitions. Each partition acts as an independent sub-table with its own set of rows, but collectively they form the complete table. Partitioning is often used to improve performance, manage data more efficiently, and facilitate maintenance operations.

Partitioning can be done based on a specific column or range of values. The database system handles the data distribution and queries efficiently, which can lead to faster query execution times and easier data management.

It has mainly two forms of partitioning:

1. Horizontal Partitioning

This partitioning split the rows of a table into multiple tables based on our logic. In horizontal partitioning, the number of columns is the same in each table, but no need to keep the same number of rows. It physically divides the table but logically treated as a whole. Currently, MySQL supports this partitioning only.

1. Vertical Partitioning

This partitioning splits the table into multiple tables with fewer columns from the original table. It uses an additional table to store the remaining columns. Currently, MySQL does not provide supports for this partitioning.

Benefits of Partitioning

The following are the benefits of partitioning in MySQL:

• It optimizes the query performance. When we query on the table, it scans only the portion of a table that will satisfy the particular statement.

• It is possible to store extensive data in one table that can be held on a single disk or file system partition.

• It provides more control to manage the data in your database.

Let's illustrate partitioning with an example using a hypothetical table for storing sales data:

Suppose we have a table named "sales_data," and it contains the following columns:

• sale_id: Unique identifier for each sale.

• sale_date: The date when the sale occurred.

• product_name: The name of the product sold.

• quantity: The quantity of the product sold.

• total_amount: The total amount of the sale.

Now, let's partition the "sales_data" table based on the "sale_date" column, partitioning it by year. This means we'll have separate partitions for sales data from each year.

In this example, we're creating a "sales_data" table with partitioning based on the "sale_date" column using the PARTITION BY RANGE clause. We specify several partitions, each covering a specific range of years. Data from each year will be stored in a separate partition:

• p2019: Contains sales data for the year 2019.

• p2020: Contains sales data for the year 2020.

• p2021: Contains sales data for the year 2021.

• p2022: Contains sales data for the year 2022.

• p2023: Contains sales data for the year 2023.

• p2024: Contains sales data for the year 2024.

When inserting data into the "sales_data" table, the database will automatically route the rows to the appropriate partition based on the "sale_date" value.

For example:

In this case, the first row will be stored in the p2022 partition, the second row in the p2021 partition, and the third row in the p2023 partition.

Partitioning can significantly improve the performance of queries that filter or aggregate data based on the partitioning column, such as queries that retrieve sales data for a specific year or a range of years. It also facilitates data management tasks like archiving or removing old data by dropping entire partitions instead of individual rows.