MySQL: OPTIMIZE TABLE needed on table with fixed columns?

Question

I have a weekly script that moves data from our live database and puts it into our archive database, then deletes the data it just archived from the live database. Since it's a decent size delete (about 10% of the table gets trimmed), I figured I should be running OPTIMIZE TABLE after this delete.

However, I'm reading this from the mysql documentation and I don't know how to interpret it: http://dev.mysql.com/doc/refman/5.1/en/optimize-table.html

"OPTIMIZE TABLE should be used if you have deleted a large part of a table or if you have made many changes to a table with variable-length rows (tables that have VARCHAR, VARBINARY, BLOB, or TEXT columns). Deleted rows are maintained in a linked list and subsequent INSERT operations reuse old row positions. You can use OPTIMIZE TABLE to reclaim the unused space and to defragment the data file."

The first sentence is ambiguous to me. Does it mean you should run it if: A) you have deleted a large part of a table with variable-length rows or if you have made many changes to a table with variable-length rows OR B) you have deleted a large part of ANY table or if you have made many changes to a table with variable-length rows

Does that make sense? So if my table has no VAR columns, do I need to run it still?

While we're on the subject - is there any indicator that tells me that a table is ripe for an OPTIMIZE call?

Also, I read this http://www.xaprb.com/blog/2010/02/07/how-often-should-you-use-optimize-table/ that says running OPTIMIZE table only is useful for the primary key. If most of my selects are from other indices, am I just wasting effort on tables that have a surrogate key?

Thanks so much!

Answer 1

In your scenario, I do not believe that regularly optimizing the table will make an appreciable difference.

First things first, your second interpretation (B) of the documentation is correct - "if you have deleted a large part of ANY table OR if you have made many changes to a table with variable-length rows."

If your table has no VAR columns, each record, regardless of the data it contains, takes up the exact same amount of space in the table. If a record is deleted from the table, and the DB chooses to reuse the exact area the previous record was stored, it can do so without wasting any space or fragmenting your data.

As far as whether OPTIMIZE only improves performance on a query that utilizes the primary key index, that answer would almost certainly vary based on what storage engine is in use, and I'm afraid I wouldn't be able to answer that.

However, speaking of storage engines, if you do end up using OPTIMIZE, be aware that it doesn't like to run on InnoDB tables, so the command maps to ALTER and rebuilds the table, which might be a more expensive operation. Either way, the table locks during the optimizations, so be very careful about when you run it.