Optimize MySQL table when query is slow

When query is slow in MySQL, you analyze it first according to



If it still doesn't work. Then you could combine the following 3 together:

optimize local table yourtable;
alter table yourtable ENGINE='InnoDB';
analyze local table yourtable;

When you run "optimize local table yourtable; ", sometimes you get the following:

Table does not support optimize, doing recreate + analyze instead.

For a table about 26M records,

optimize local table yourtable; 

and

alter table yourtable ENGINE='InnoDB';   

takes about 30 to 45 minutes. We care about it because it will lock the table.

For

analyze local table yourtable;

it is instantly.

Comments

Popular posts from this blog

Check MySQL query history from command line

Fixed: ImportError: No module named utilities.common.tools