MySQL Forums
Forum List  »  Optimizer & Parser

Re: Different Servers Using Different Index for Same Query on Same Data
Posted by: Rick James
Date: April 01, 2011 08:41AM

Quote

For InnoDB tables, OPTIMIZE TABLE is mapped to ALTER TABLE, which rebuilds the table to update index statistics and free unused space in the clustered index.
-- http://dev.mysql.com/doc/refman/5.0/en/optimize-table.html

But note that OPTIMIZE (ALTER) involves copying over the entire table. So it is costly for large tables.

The clustered index (PRIMARY KEY in InnoDB) is structured as a BTree. As you delete rows, the associated blocks get emptier. When they get 'too' empty, the engine tries to coalesce two adjacent blocks, thereby freeing a block. OPTIMIZE is more aggressive at freeing blocks. But it is unclear whether OPTIMIZE does that much better at improving the statistics. The stats are still at the mercy of the 8 not-so-random probes.

Jorgen -- I like your explanations; keep it up. :) You might also like the Performance forum.

Options: ReplyQuote




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.