mysql-maintenance

PROCEDURE ANALYZE

It is common that a table is created with conservative settings such as larger data type, or hestitate using ENUM. After running the database for a period of time, the actually data pattern will be eventually outlined. To easily determine if a table can be re-configured with smaller data type, you can use the PROCEDURE ANALYZE statement.

PROCEDURE ANALYZE() can help you determine whter columns can be redefined to a smaller data types. It can also etermine whether a column contains only a small number of values and could be defined as an enum.

select * from sales_order_head procedure analyse(10,256) \G
CHECK TABLE BR201312111529.SALES_ORDER_HEAD;
REPAIR TABLE  BR201312111529.SALES_ORDER_HEAD;

The Analyze table statement updates a table with information about the distribution of key values in the table. This information is used by the optimizer to make better choices about query execution plans.  This statement works for MyISAM and InnoDB tables.

The ANALYZE TABLE command should be used to improve performance by updating index distribution statistic of after large amounts of table data have changed.

ANALYZE TABLE   BR201312111529.SALES_ORDER_HEAD;

THE OPTIMIZE TABLE statement cleans up a MyISAM table by defragmenting it. This involves reclaming unused space resulting from deletes and updates, and coalescing records that have become split and sttore non-contiguously. OPTIMIZE TABLE also sorts the index pages if they are out of order and updates the index statistics.

OPTIMIZE TABLE   BR201312111529.SALES_ORDER_HEAD;

mysqlcheck --database world sample -uroot -pPassword