Re: Mysql DB Optimization
Posted by:
Rick James
Date: April 20, 2015 11:38PM
OPTIMIZE TABLE is almost never worth doing for InnoDB tables. Even for MyISAM tables, it is rarely useful.
InnoDB has everything structured in BTrees -- one for the Data and PRIMARY KEY, one for each secondary index.
A BTree is mostly 'stable', hence, there is not much to 'defragment' out of it.
OPTIMIZE is implemented as rebuilding the table and its indexes. The actual order of reinserting rows into table (during OPTIMIZE) is likely to be different than when the rows were originally inserted. Hence, block splits (natural in BTrees), etc, are different, and _could_ lead to more blocks than it started with.
Don't use OPTIMIZE TABLE.
Subject
Views
Written By
Posted
1708
April 17, 2015 01:00PM
Re: Mysql DB Optimization
828
April 20, 2015 11:38PM
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion
of Oracle or any other party.