Brian: /* How to Optimize and Repair your MySQL Tables */

2013-11-15T21:08:56Z

‎How to Optimize and Repair your MySQL Tables

Brian: Created page with "==How to Optimize and Repair your MySQL Tables== Does your database feel like it has slowed down? Your pages starting to load slower and slower? It might be time to some dat..."

2013-11-15T21:06:37Z

Created page with "==How to Optimize and Repair your MySQL Tables== Does your database feel like it has slowed down? Your pages starting to load slower and slower? It might be time to some dat..."

New page

==How to Optimize and Repair your MySQL Tables==

Does your database feel like it has slowed down? Your pages starting to load slower and slower? It might be time to some database optimizations.

'''ANALYZE TABLE <table_name>'''

First thing I run when check tables it ANALYZE TABLE which analyes and stores the key distribution for the table. During the analysis, the table is locked with a read lock for InnoDB and MyISAM.

'''REPAIR TABLE <table_name>'''

REPAIR TABLE only applies to MyISAM, ARCHIVE, and CSV tables. See Section 14.3, The MyISAM Storage Engine, and Section 14.6, The ARCHIVE Storage Engine, and Section 14.5, The CSV Storage Engine

'''OPTIMIZE TABLE <table_name>'''

For InnoDB tables, OPTIMIZE TABLE is mapped to ALTER TABLE, which rebuilds the table to update index statistics and free unused space in the clustered index.

Looking a one liner that will do repair, check and optimize on all databases. I would recommend setting up an alias in your bash, so you do not put your root password in your bash history.
'''mysqlcheck -u root --password=THEPASSWORD --auto-repair --check --optimize --all-databases'''

====Additional Articles====
* [[MySQL - Check Which Query is Consuming Resources]]
* [[Rest your MySQL Admin Password]]
* [[Changing the Minimum Search Query - MySQL Full Text Only]]
* [[Quit Bash Shell without Saving Bash History]]

@@ Line 16: / Line 16: @@
 Looking a one liner that will do repair, check and optimize on all databases.  I would recommend setting up an alias in your bash, so you do not put your root password in your bash history.
   '''mysqlcheck -u root --password=THEPASSWORD --auto-repair --check --optimize --all-databases'''
 ====Additional Articles====

How to Optimize/Repair a Table - Revision history

Brian: /* How to Optimize and Repair your MySQL Tables */

Brian: Created page with "==How to Optimize and Repair your MySQL Tables== Does your database feel like it has slowed down? Your pages starting to load slower and slower? It might be time to some dat..."