Mysql: What are fragmented tables and why do we need to run OPTIMIZE?
We recently started optimizing a database that never met any standards. However, we must do the following.
clean up of unused tables.
optimizing data types and indexes.
tuning slow queries.
evaluating my.cnf configurations with mysqltuner.pl and similar ones
In this process, I am running into OPTIMIZING fragmented tables. What is this fragmented table and how will it affect functionality and performance. Why would I run OPTIMIZE on these tables ...?
I believe this won't affect functionality at all. please correct me if not.
Thanks for answers. Best regards, Uday
source to share
When you delete records (or update them to get smaller), there will be "holes" in the table, chunks of empty data. They will be filled with new data if they fit in the holes, but depending on how you use the table, there can be a significant amount of wasted space left. This is not good because now you need to read more blocks from disk for the same amount of useful information.
Running an optimization command on this fragmented table will move records around to get rid of holes in the table.
After performing major maintenance or upgrades (such as bulk deletes), it is usually recommended to do so.
source to share