How can I rebuild indexes and update stats in MySQL innoDB

Question

I have experience with MS SQL server where it is possible and useful to update statistic and rebuild indexes  I can t find such option in MySQL innoDB  is there such option  If not  how MySQL database create an execution plan  Does the MySQL update indexes and statistic with every UPDATE and INSERT

User · Accepted Answer

This is done with  ANALYZE TABLE table name    Read more about it here      ANALYZE TABLE analyzes and stores the key distribution for a table  During the analysis  the table is locked with a read lock for MyISAM  BDB  and InnoDB  This statement works with MyISAM  BDB  InnoDB  and NDB tables

User · Answer

Why   One almost never needs to update the statistics   Rebuilding an index is even more rarely needed   OPTIMIZE TABLE tbl  will rebuild the indexes and do ANALYZE  it takes time   ANALYZE TABLE tbl  is fast for InnoDB to rebuild the stats   With 5 6 6 it is even less needed

User · Answer

To date  mysql 8 0 18  there is no suitable function inside mysql to re-create indexes  Since mysql 8 0 myisam is slowly phasing into deprecated status  innodb is the current main storage engine  In most practical cases innodb is the best choice and it s supposed to keep indexes working well  In most practical cases innodb also does a good job  you do not need to recreate indexes  Almost always     When it comes to large tables with hundreds of GB data amd rows and a lot of writing the situation changes  indexes can degrade in performance  In my personal case I ve seen performance drop from  15 minutes for a count    using a secondary index to 4300 minutes after 2 months of writing to the table with linear time increase  After recreating the index the performance goes back to 15 minutes      To date we have two options to do that  1  OPTIMIZE TABLE   or ALTER TABLE  Innodb doesn t support optimization so in both cases the entire table will be read and re-created  This means you need the storage for the temporary file and depending on the table a lot of time  I ve cases where an optimize takes a week to complete   This will compact the data and rebuild all indexes  Despite not being officially recommended  I highly recommend the OPTIMIZE process on write-heavy tables up to 100GB in size     2  ALTER TABLE DROP KEY -  ALTER TABLE ADD KEY You manually drop the key by name  you manually create it again  In a production environment you ll want to create it first  then drop the old version  The upside  this can be a lot faster than optimize  The downside  you need to manually create the syntax   SHOW CREATE TABLE   can be used to quickly see which indexes are available and how they are called     Appendix  1   To just update statistics you can use the already mentioned  ANALYZE TABLE   2   If you experience performance degradation on write-heavy servers you might need to restart mysql  There are a couple of bugs in current mysql  8 0  that can cause significant slowdown without showing up in error log  Eventually those slowdowns lead to a server crash but it can take weeks or even months to build up to the crash  in this process the server gets slower and slower in responses  3   If you wish to re-create a large table that takes weeks to complete or fails after hours due to internal data integrity problems you should do a CREATE TABLE LIKE  INSERT INTO SELECT    then  atomic RENAME  the tables  4   If INSERT INTO SELECT   takes hours to days to complete on huge tables you can speed up the process by about 20-30 times using a multi-threaded approach  You  partition  the table into chunks and INSERT INTO SELECT   in parallel

User · Answer

For basic cleanup and re-analyzing you can run  OPTIMIZE TABLE       it will compact out the overhead in the indexes and run ANALYZE TABLE too  but it s not going to re-sort them and make them as small  amp  efficient as they could be   https   dev mysql com doc refman 8 0 en optimize-table html  However  if you want the indexes completely rebuilt for best performance  you can    drop   re-add indexes  obviously  dump   reload the table ALTER TABLE and  change  using the same storage engine REPAIR TABLE  only works for  MyISAM  ARCHIVE  and CSV    https   dev mysql com doc refman 8 0 en rebuilding-tables html  If you do an ALTER TABLE on a field  that is part of an index  and change its type  then it will also fully rebuild the related index es

User · Answer

You can also use the provided CLI tool mysqlcheck to run the optimizations  It s got a ton of switches but at its most basic you just pass in the database  username  and password   Adding this to cron or the Windows Scheduler can make this an automated process   MariaDB but basically the same thing

[mysql] How can I rebuild indexes and update stats in MySQL innoDB?

Examples related to mysql

Examples related to innodb