I have a weekly script that moves data from our live database and puts it in our archive database, and then deletes the data that it just archived from the live database. Since this is a decent delete size (about 10% of the table is cropped), I decided that after that delete the OPTIMIZE TABLE.
However, I am 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 most of the table or made many changes to the table with variable-length rows (tables with VARCHAR, VARBINARY, BLOB or TEXT columns). Rows are maintained in a linked list and subsequent INSERT operations reuse old row positions "You can use OPTIMIZE TABLE to recover unused space and defragment the data file."
The first sentence is ambiguous for me. Does this mean that you should run it if: A) you deleted most of the table with variable-length rows or if you made many changes to the table with variable-length rows OR B) you deleted most of the ANY table or made many changes to the table with variable length strings
It makes sense? So, if there are no VAR columns in my table, do I still need to run it?
While we are talking about the subject - is there any indicator that tells me that the table is ripe for calling OPTIMIZE?
Also, I read this http://www.xaprb.com/blog/2010/02/07/how-often-should-you-use-optimize-table/ , which says that a working OPTIMIZE table is only useful for the primary the key. If most of my samples relate to other indexes, am I just wasting effort on surrogate key tables?
Many thanks!
source
share