Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

Tags:

mysql

innodb

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?

like image 251
Tomas Kubes Avatar asked May 05 '15 11:05

Tomas Kubes


People also ask

Do I need to update statistics after rebuilding index?

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.

Can we rebuild indexes in MySQL?

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.

Which command rebuilds tables into the InnoDB storage system?

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.


1 Answers

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.

like image 176
fancyPants Avatar answered Oct 05 '22 23:10

fancyPants