A huge used database reverberate on a more fragmented database even if you delete any large data. The data base admin should optimize and take care of this especially if dealing with a lot of varying characters (VARCHAR). At this article I will explain how to opmimize MySQL databases with tools that mysql provides. I will separate on two cases:
- Optimizing just one table
- Optimizing all the tables on a database
The main disvantage is that you can only optimize MyISAM, InnoDB, and ARCHIVE tables.
Optimizing a single table is very easy. Here is the command that how we can optimize MySQL database single table with command line.
OPTIMIZE TABLE my_table;
I think that is self-explanatory but if you have some doubt refer to MySQL OPTIMIZE documentation.
The above command only work on single table so if you want to optimize whole database in just one step you should worl with mysqlcheck command:
mysqlcheck -op -u granted_user your_database_name
Let me explain this command. The ‘o’ parameter refers to optimize, the ‘p’ parameter is for force mysql to ask us for a password, the -u parameter is for insert a custom user to connecto to MySQL server. Take notice that the above user should have appropriate right to access that database. Finally if you want to optimize all MySQL databases then do this.
mysqlcheck -op -u granted_user –all-databases
You can also repair MySQL database with mysqlcheck, just replace the ‘o’ parameter with‘r’ instead e.g.
mysqlcheck -rp your_database_name