Menu
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?
251
Operations such as rebuilding, defragmenting, or reorganizing an index do not change the distribution of data. Therefore, you do not need to update statistics after performing ALTER INDEX REBUILD, DBCC DBREINDEX, DBCC INDEXDEFRAG, or ALTER INDEX REORGANIZE operations.
MySQL REINDEX denotes the re-indexing process to rebuild the indexes on a database if the database is corrupted or needs repair to optimize the tables and fetch the rows using indexes properly using phpMyAdmin.
If you need to rebuild an InnoDB table because a CHECK TABLE operation indicates that a table upgrade is required, use mysqldump to create a dump file and mysql to reload the file.
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.
176
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
