MySQL Bugs: #42822: optimize partition recreates full table instead just partition

Bug #42822	optimize partition recreates full table instead just partition
Submitted:	13 Feb 11:24
Reporter:	Bogdan Kecman
Status:	Verified
Category:	Server: Partition	Severity:	S3 (Non-critical)
Version:	5.1.30	OS:	Any
Assigned to:	Mattias Jonsson	Target Version:
Triage:	Triaged: D3 (Medium) / R3 (Medium) / E3 (Medium)

Description:
InnoDB table with multiple partitions,
when ALTER TABLE OPTIMIZE PARTITION on only ONE partition is run,
ALL partitions are copied to /tmp files and rebuilt.

When running the command ALTER TABLE REBUILD PARTITION on only ONE partition,
only ONE partition is copied to a tmp file.

The ALTER TABLE OPTIMIZE PARTITION command should only optimize the partitions specified.

How to repeat:
execute ALTER TABLE OPTIMIZE PARTITION on a single partition of innodb table

Suggested fix:
.

The workaround is to do:
ALTER TABLE t REBUILD PARTITION <list of partitions>
ALTER TABLE t ANALYZE PARTITION <list of partitions>

Which is the way it is done for innodb.

Since innodb does not support optimize and suggest ALTER instead, mysql recreates the
table and then doing analyze on it afterwards, so this workaround is just to be explicit
about it.

See also bug#20129

not fixed in 5.1.39 (from mysql labs)