analyze table, optimize table, how often?

Question

in mysql i have 3 tables. one is 500,000, another 300,000, and finally around 5,000

they each get maybe 50-500 additional rows daily

should i run analyze and optimize table on them? if so then how often?

Answer 1

optimize table rebuilds the table for InnoDB so it could take a wicked long time to run. It's used for reclaiming space and recreating indexes. I'd say run that rarely if at all. optimize table doc

analyze should be redone whenever the overall distribution of the indexed data changes significantly. So if you're inserting the same type of stuff at the same rate over time - no need to run analyze often - do it maybe once a month. But if things change drastically - such that you get way more of one type of data than another or something else unusual - then run it afterwards.

I run it for example after loading a new table with data and perhaps a good idea would be to run it against all like once a week if you have no clue.