Improving MySQL Query and Maintenance Performance with Partition Tables

MySQL currently uses the InnoDB engine, which lacks a native MERGE engine for table sharding but provides native partition tables that split records horizontally and are transparent to applications.

Key advantages of partition tables

Significant improvement of certain query performances.

Simplified daily data operations and increased operational efficiency.

Parallel query execution and balanced write I/O.

Application transparency—no need for routing or middleware.

Practical examples of performance gains

Query performance (range query)

Splitting a suitable partition table reduces the number of scanned rows for the same query, making it far more efficient than a non‑partitioned table.

Table t1 is a non‑partitioned table; p1 is its partitioned counterpart, both containing 10 million rows.

localhost:ytt> show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int NOT NULL,
  `r1` date DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

localhost:ytt> show create table p1\G
*************************** 1. row ***************************
       Table: p1
Create Table: CREATE TABLE `p1` (
  `id` int NOT NULL,
  `r1` date DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50100 PARTITION BY RANGE (`id`)
(PARTITION p0 VALUES LESS THAN (1000000) ENGINE = InnoDB,
 PARTITION p1 VALUES LESS THAN (2000000) ENGINE = InnoDB,
 ...
 PARTITION p9 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */

Execution plans for a range query ( id < 1000000) show that the partitioned table p1 has a much lower cost and scans fewer rows than t1.

localhost:ytt> explain format=tree select count(*) from t1 where id < 1000000\G
EXPLAIN: -> Aggregate: count(0)
    -> Filter: (t1.id < 1000000)  (cost=407495.19 rows=2030006)
        -> Index range scan on t1 using PRIMARY  (cost=407495.19 rows=2030006)

localhost:ytt> explain format=tree select count(*) from p1 where id < 1000000\G
EXPLAIN: -> Aggregate: count(0)
    -> Filter: (p1.id < 1000000)  (cost=99980.09 rows=499369)
        -> Index range scan on p1 using PRIMARY  (cost=99980.09 rows=499369)

Similarly, for an inequality query ( id != 2000001), the partitioned table exhibits lower cost and fewer scanned rows.

localhost:ytt> explain format=tree select count(*) from t1 where id != 2000001\G
... cost=1829866.58 rows=9117649 ...

localhost:ytt> explain format=tree select count(*) from p1 where id != 2000001\G
... cost=1002750.23 rows=4993691 ...

Update performance (filtered UPDATE)

For UPDATE statements with a range filter, the partitioned table scans far fewer rows.

localhost:ytt> explain update t1 set r1 = date_sub(current_date,interval ceil(rand()*5000) day) where id between 1000001 and 2990000\G
... rows: 3938068 ...

localhost:ytt> explain update p1 set r1 = date_sub(current_date,interval ceil(rand()*5000) day) where id between 1000001 and 2990000\G
... rows: 998738 ...

Operational benefits: data exchange

Specific partitions of a partitioned table can be exported and imported easily, allowing fast data exchange with a non‑partitioned table.

localhost:ytt> create table t_p1 like t1;
Query OK, 0 rows affected (0.06 sec)

localhost:ytt> alter table p1 exchange partition p1 with table t_p1;
Query OK, 0 rows affected (0.07 sec)

-- After exchange, p1 loses 1 million rows, t_p1 gains them.

Manual exchange for a non‑partitioned table requires multiple steps: select data, import, delete original rows, and repeat in reverse, which is more labor‑intensive and error‑prone.

Log size reduction

Partition exchange generates far smaller binary logs compared to manual data movement.

yt t1> reset master;
Query OK, 0 rows affected (0.02 sec)

-- After a partition exchange, log file ytt1.000001 is only 588 bytes, whereas a manual insert creates a 7.2 MB log (ytt1.000002).

Quick cleanup of a single partition

Truncating a single partition is much faster than deleting a range of rows from a non‑partitioned table.

localhost:ytt> alter table p1 truncate partition p0;
Query OK, 0 rows affected (0.07 sec)

-- Deleting the same range in t1 takes dozens of seconds.
localhost:ytt> delete from t1 where id < 1000000;
Query OK, 999999 rows affected (26.80 sec)

Summary

MySQL partition tables are highly efficient in many scenarios. This article introduced their basic advantages for simple queries and routine operations; future articles will explore additional use cases.