Compress at partition level

MySQL & MariaDB Percona Server for MySQL 8.0
1

Hello,
I’m aware it’s not possible to compress at a partition level. I was wondering if others would find this feature useful or if there are any plans to implement it?
Thanks.

2

To which database are you referring?

3

Apologies. MySQL 8.0. We have some large partitioned audit tables. It would be very useful if we could compress the older partitions.

I did experiment with creating a compressed table per year and then using a View for selects. The performance sucked. The indexes on the underlying tables were not used.

4

Who said you can’t compress partitioned tables?

CREATE TABLE `members` (
  `firstname` varchar(25) NOT NULL,
  `lastname` varchar(25) NOT NULL,
  `username` varchar(16) NOT NULL,
  `email` varchar(35) DEFAULT NULL,
  `joined` date NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=COMPRESSED
PARTITION BY RANGE (year(`joined`))
(PARTITION p0 VALUES LESS THAN (1960) ENGINE = InnoDB,
 PARTITION p1 VALUES LESS THAN (1970) ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN (1980) ENGINE = InnoDB,
 PARTITION p3 VALUES LESS THAN (1990) ENGINE = InnoDB,
 PARTITION p4 VALUES LESS THAN MAXVALUE ENGINE = InnoDB);

Why do you want to only compress specific partitions? Just compress the whole table. What are you losing by doing that?