title: MySQL - rebuild partition vs optimize partition
body: <p>I've partitioned tables in my MySQL 5.1.41 which hold very huge amount of data. Recently, I've deleted a lot of data which caused fragmentation of around 500 GB yet there is a lot of data in the partitions.</p>
  
  <p>To reclaim that space to the OS, I had to de-fragment the partitions. I referred to MySQL documentation, <a href="https://dev.mysql.com/doc/refman/5.1/en/partitioning-maintenance.html" rel="nofollow">https://dev.mysql.com/doc/refman/5.1/en/partitioning-maintenance.html</a> which confused me with the following statements,</p>
  
  <blockquote>
    <p><strong>Rebuilding partitions :</strong> Rebuilds the partition; this has the same effect as dropping all records stored in the partition, then
    reinserting them. This can be useful for purposes of defragmentation.</p>
    
    <p><strong>Optimizing partitions :</strong> If you have deleted a large number of rows from a partition or if you have made many changes to a partitioned
    table with variable-length rows (that is, having VARCHAR, BLOB, or
    TEXT columns), you can use ALTER TABLE ... OPTIMIZE PARTITION to
    reclaim any unused space and to defragment the partition data file.</p>
  </blockquote>
  
  <p>I tried both and observed sometimes "rebuild" happens faster and sometimes "optimize". Each partition I run these commands on, has records from millions to sometimes billions. I'm aware of what MySQL does for above each statement.</p>
  
  <p>Do they need to be applied based on number of rows in the partition? If so, on how many rows I can use "optimize" and on how many I should use "rebuild"? </p>
  
  <p>Also, which is better to use?</p>