Partition in MySQL

Partition in MySQL

In the article, the use of partition in MySQL is explained with various examples.

What is Partition in MySQL?

We can translate the partition word used in MySQL and advanced DBMS systems as division and separation.

Regardless of the normalization and index structure of the database design, as the data grows, the table will start to slow down after a while.

Partition, and distributed MySQL servers can be used to prevent slowdown.

Using the partition feature, certain parts of the data are physically divided into pieces.

For example; Let’s say we have 2000 records in the products table with product information. We can divide the table into sections, with the first 1000 records in the first section and the second 1000 records in the second section.

Similarly, we can divide the table by date by years, months and days.

The partition property is used only in columns that contain numeric data or that can be converted into numeric data.

Partition Types in MySQL

Partition types consist of four parts: RANGE, LIST, HASH and KEY.

RANGE Partititon in MySQL

It is used to partition the column by a certain range.

How To Create Range Partition in MySQL

You can access information about the partitions created with the following commands.

LIST Partition in MySQL

It is used to divide the column by specific value.

How To Create List Partition in MySQL

As a result of the command, in the p0 section, the values of product_id value 1, 2 and 5 will be stored. p1, p2, p3 values will also store the values written in parentheses.

HASH and KEY Partitions in MySQL

It performs partitioning according to special algorithmic expression.

If KEY is selected as the partition type, it processes based on the PRIMARY KEY or UNIQUE KEY in the table.

How To Create HASH and KEY Partitions in MySQL

If there is no PRIMARY KEY or UNIQUE KEY in the table, it will throw an error.

Similarly, partitioning can be done by selecting PRIMARY KEY or UNIQUE KEY column.

The HASH partition type is created similar to the KEY partition type.

However, the processes performed in the background are different.

Although partitioning is done, if a good query is not created, there may not be an increase in performance.

You can select partition in query expressions to increase performance.

In the example below, in the p0 section, that is, in the section with the first 1000 records, we query the values with the value of product_id below 250.

Similarly, it will be effective to write the column name that is partitioned as the first search criterion in queries.

The most appropriate partition type should be selected by performing performance tests after partitioning.

Also, ANALYZE, OPTIMIZE, CHECK, REPAIR are used as optimization commands for partitioning.

I wish you good day.

 

Leave a Reply