Understanding Multi-Column Partition Tables in MySQL: Implementation, Performance Comparison, and Best Practices

MySQL supports both single‑column and multi‑column partitioning, similar to composite indexes. Creating a multi‑column partitioned table involves defining a RANGE COLUMNS partition on fields (r1, r2, r3) and can be automated with a stored procedure. select * from p1 where f1 = 2 and f2 = 2 and f3 = 2; The following stored procedure generates 201 partitions for a table p1 with columns r1, r2, r3, and log_date, handling values 1‑8 for r1 and 1‑5 for r2 and r3.

DELIMITER $$
USE `ytt_new`$$
DROP PROCEDURE IF EXISTS `sp_add_partition_ytt_new_p1`$$
CREATE DEFINER=`root`@`%` PROCEDURE `sp_add_partition_ytt_new_p1`()
BEGIN
    DECLARE i,j,k INT UNSIGNED DEFAULT 1;
    SET @stmt = '';
    SET @stmt_begin = 'ALTER TABLE p1 PARTITION BY RANGE COLUMNS (r1,r2,r3)(';
    WHILE i <= 8 DO
        SET j = 1;
        WHILE j <= 5 DO
            SET k = 1;
            WHILE k <= 5 DO
                SET @stmt = CONCAT(@stmt,' PARTITION p',i,j,k,' VALUES LESS THAN (',i,',',j,',',k,'),');
                SET k = k + 1;
            END WHILE;
            SET j = j + 1;
        END WHILE;
        SET i = i + 1;
    END WHILE;
    SET @stmt_end = 'PARTITION p_max VALUES LESS THAN (maxvalue,maxvalue,maxvalue))';
    SET @stmt = CONCAT(@stmt_begin,@stmt,@stmt_end);
    PREPARE s1 FROM @stmt;
    EXECUTE s1;
    DROP PREPARE s1;
END$$
DELIMITER ;

Calling the procedure creates a multi‑column partitioned table with 201 partitions and 5 000 000 rows. call sp_add_partition_ytt_new_p1; A single‑column partitioned table p2 is created on column r1 with only nine partitions for comparison.

CREATE TABLE `p2` (
  `r1` int DEFAULT NULL,
  `r2` int DEFAULT NULL,
  `r3` int DEFAULT NULL,
  `log_date` datetime DEFAULT NULL
) ENGINE=InnoDB
PARTITION BY RANGE COLUMNS(r1)
(PARTITION p1 VALUES LESS THAN (1),
 PARTITION p2 VALUES LESS THAN (2),
 ...,
 PARTITION p_max VALUES LESS THAN (MAXVALUE));
INSERT INTO p2 SELECT * FROM p1;

Performance tests show that when all three columns are filtered, the multi‑column table p1 executes in 0.02 s versus 0.49 s for p2, a difference of dozens of times.

select count(*) from p1 where r1 = 2 and r2 = 2 and r3 = 2;  -- 0.02 sec

select count(*) from p2 where r1 = 2 and r2 = 2 and r3 = 2;  -- 0.49 sec

Explain plans confirm that p1 scans only about 20 k rows, while p2 scans over 600 k rows.

When only two columns are filtered, p1 still outperforms p2 (0.10 s vs 0.52 s). With a single column filter, p2 can be slightly faster.

The order of partition columns matters when the query does not include all columns. For example, a table p3 partitioned by (r2, r3, r1) runs the query select count(*) from p3 where r2 = 1 and r3 = 4; in 0.22 s, whereas the same query on p1 takes 5.05 s because p1 must scan many more partitions.

Thus, multi‑column partitioning behaves similarly to composite indexes: it can dramatically improve query performance in scenarios where the partition key matches the query predicates, but careful consideration of column frequency and order is required.