All we need is an easy explanation of the problem, so here it is.
After a change of data type or index for exemple. Given some queries to do maintenance tasks. What’s the best order of doing these tasks?
ANALYSE TABLE foo; CHECK TABLE foo; OPTIMIZE TABLE foo; REPAIR TABLE foo; ALTER TABLE foo Engine=X; --defragment task
Does order matter? Like after an OPTIMIZE (which arrange disk space) which could eventually lead to corruption, it’s better to do a CHECK after?
Does one task affect another and does one task is a repeatation of another? Like I have the feeling that OPTIMIZE is the same as defragment and CHECK is the same as REPAIR.
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
It depends on what storage engine you use. The default storage engine has been InnoDB since MySQL 5.5 (circa 2010), so I will assume that’s what you use, since you did not specify.
OPTIMIZE TABLE <name>does the same as
ALTER TABLE <name> FORCEor
ALTER TABLE <name> ENGINE=InnoDB. This should be done with care, because it rebuilds the whole table, and that locks the table and takes a long time, proportional to the size of the table and number of indexes. Because it copies data pages to a new table, and rebuilds indexes, this accomplishes defragmentation. It also accomplishes the same as
ANALYZE TABLE <name>. I almost never run OPTIMIZE TABLE. I prefer to use pt-online-schema-change to defragment InnoDB tables, because that does its job without blocking clients from reading or writing the table.
ANALYZE TABLE <name>accomplishes a resampling of pages to update table and index statistics. You can do this anytime. It is quick regardless of the size of the table, and it does not lock the table. At my workplace, I developed a scheduled task to run ANALYZE TABLE on all the tables about every ten days (it runs every day, and analyzes 1 out of 10 tables each time).
CHECK TABLE <name>validates the pages of the table for corruption. If a corrupt table or index is detected, this may halt the MySQL Server. Refer to documentation for details. FWIW, I don’t think I’ve ever run CHECK TABLE on an InnoDB table.
REPAIR TABLE <name>does nothing for InnoDB. It works on MyISAM, ARCHIVE, and CSV tables. I have definitely never run this on any InnoDB table.
I can’t think of any reason you would run all of these statements on the same table during the same session, regardless of the order you run them in.
Short answer: Never.
Seriously. InnoDB takes care of itself. None of those actions are needed on a regular basis. Only if you have some abnormal activity might you need one of them.
If you are using MyISAM, my first advice is, "don’t". But if you must, then here is my advice (taken from several years of use, but long ago):
CHECK TABLE + REPAIR TABLE -- needed only after a crash OPTIMIZE TABLE only once a month, and only on tables that become badly fragmented ANALYZE TABLE -- probably never ALTER TABLE t ENGINE=MyISAM -- never; See OPTIMIZE
SHOW TABLE STATUS LIKE 'tablename'; will show Data_length, Index_length and Data_free. If Data_free is more than Data_length, then you could call the table "fragmented" and consider using
OPTIMIZE. (This info is also available via a
SELECT ... FROM information_schema.tables ....) Caution: for InnoDB or partitioned tables, it is likely to be misleading.
If you are using some other engine, then I probably don’t have any advice.
ANALYZE and the Optimizer — In the old days, there were several ways in which the "statistics" could get out of wack. This would rear its ugly head with a query using the "wrong" index and run slower "that it should". This has mostly been cured for InnoDB in about 5.6; older versions and non-InnoDB tables may still experience the problem.
ANALYZE's sole goal is to recompute the statistics on command. Caution: There are times when it makes things worse. (This depends on version, engine, and mostly data distribution.)
For more than a decade, MySQL has focused on InnoDB and mostly ignored MyISAM. Some of the strides include eliminating (or at least lessening) the need for
The design of the data layout of InnoDB inherently avoids several of the optimization problems that MyISAM can encounter. In over 2 decades of using MySQL, I have identified only 2 tables that needed regular (monthly was adequate)
OPTIMIZE. Both were MyISAM.
Oh, another point. If you delete a lot of rows, Data_free is likely to become large. Whereas Optimize cleans it up, it may be much better to redesign the big
DELETE. See: http://mysql.rjweb.org/doc.php/deletebig
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂