Search This Blog

Tuesday, November 13, 2018

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.