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?
To which database are you referring?
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.
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?