Bug #94016 Support per-partition optimization for paritioned InnoDB tables
Submitted: 23 Jan 10:04 Modified: 23 Jan 12:09
Reporter: Alexey Kopytov Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Partitions Severity:S4 (Feature request)
Version:8.0 OS:Any
Assigned to: CPU Architecture:Any
Triage: Needs Triage: D5 (Feature request)

[23 Jan 10:04] Alexey Kopytov
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
[23 Jan 12:09] Umesh Shastry
Hello Alexey,

Thank you for the feature request!

regards,
Umesh