The database become more fragmented whenever you delete any large data from your sql database. It is always a very good practice to optimize your database time by time especially if you are dealing with a lot of varying characters (VARCHAR).

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  myTBL

The above command only work on single tabe but if you want to optimizte whole database in a single command then we should play with mysqlcheck, here is the solution to optimize complete database with single command.

mysqlcheck -op your_database_name

• The ‚Äòo‚Äô refers to optimize
• The ‚Äòp‚Äô is for password requirement. You‚Äôll be prompted for the password. If you are logged in as ‘root’ user then you have to enter mysql root’s password. But if you don’t want to use root user then you can use -u option like this.

mysqlcheck -op -u username your_database_name

If you want to optimize all MySQL databases then do this.

mysqlcheck -op -u user_name –all-databases

You can also repair MySQL database with mysqlcheck for this you can use option ‘r’ instead of option ‘o’ e.g.

mysqlcheck -rp your_database_name

That’s all you have successfully repair and optimize your database with very handy tool (mysqlcheck).

The above practice can be achieve through phpMyAdmin. As you know phpMyAdmin is a web based MySQL administration tool.

Optimization of MySQL databases is quite easy when using the phpMyAdmin tool. If you are using hosting services it is available in cPanel.

To optimization MySQL database’s tables, just login to your phpMyAdmin and select the database. A list will be appear in front of you. Just tick the tables you wish to optimize, or simpley click on [Check All] to select all tables.

Then click the box [With selected:] and choose Optimize table. This will execute the OPTIMIZE TABLE SQL query on the selected tables and they all will be updated and optimized.

I hope you’ll enjoy this howto