Description:
The manual currently says the following on optimizing partitioned InnoDB
tables:
"InnoDB does not currently support per-partition optimization; ALTER
TABLE ... OPTIMIZE PARTITION causes the entire table to rebuilt and
analyzed, and an appropriate warning to be issued. (Bug #11751825, Bug
#42822) To work around this problem, use ALTER TABLE ... REBUILD
PARTITION and ALTER TABLE ... ANALYZE PARTITION instead."
https://dev.mysql.com/doc/refman/8.0/en/alter-table-partition-operations.html
Indeed, that is still the case even in 8.0:
mysql> create table t1(c1 int) partition by hash (c1) partitions 4;
Query OK, 0 rows affected (0.07 sec)
mysql> alter table t1 optimize partition p1\G
*************************** 1. row ***************************
Table: test.t1
Op: optimize
Msg_type: note
Msg_text: Table does not support optimize on partitions. All partitions will be rebuilt and analyzed.
*************************** 2. row ***************************
Table: test.t1
Op: optimize
Msg_type: status
Msg_text: OK
2 rows in set (0.12 sec)
However, since bug #42822 was closed by documenting the limitation and I
was unable to find a feature request to lift it, I'm filing it here.
How to repeat:
create table t1(c1 int) partition by hash (c1) partitions 4;
alter table t1 optimize partition p1\G