When a database begins to have a considerable size it is highly advisable to keep its tables in good condition. In this article we explain how to repair and optimize MySQL databases, in graphical mode and by command line.
In other articles we have already dealt with topics related to databases, for example how to import and export MySQL databases from the console or how to connect to MySQL from PHP.
Before we start, it is very important that, to heal ourselves in health, we make a backup copy of the database.
Graphics mode
To repair and optimize database tables graphically we will use the phpMyAdmin tool. From the table view, we will select all those that interest us and we will mark the option to Repair the Table:
PhpMyAdmin control panel
Next, we will see a message of the result. The next step is to optimize the database. The procedure is the same, but selecting the Optimize table option.
Command console
From the Linux command console we have the mysqlcheck tool available. To use this command we can do the following, depending on what we want to achieve:
// Optimization of all databases
mysqlcheck -o –all-databases
// Analysis and repair of all databases
mysqlcheck –all-databases -r
// Analysis and repair of the databases that we indicate
mysqlcheck -r db_name
Apart from these commands, the mysqlcheck tool offers us a multitude of options. The basic structure is as follows:
mysqlcheck [options] [db_name [table_name …]]
For more information, we can consult the MySQL reference manual.
Why or why do this?
All this is fine, but … why would I want to repair and / or optimize my databases? At the beginning of this article we mentioned that it is advisable to perform this maintenance when a database begins to grow considerably, but why?
If you have been working with databases for a long time, you may have ever come across a message similar to:
“Table marked as crashed and should be repaired”
This means that the table has been “broken” or corrupted. This can be due to a hard drive failure, a sudden server restart, problems with the BIOS firmware, and other reasons.
When we have a problem of this type, we have to try to launch a repair of the database as we have explained.
In the case of optimization, it is necessary in the same way that our hard disk needs to be defragmented from time to time. The flow of information in a large database is usually constant, therefore, it must be organized in an optimal way.
What the database optimization commands do is reorganize the physical storage of the data in the tables and their associated indexes, thereby reducing the space required and considerably improving queries and access time to information.
If the database of your website stores a lot of information, you can hire our web maintenance service, with which all your information will always be in the best state without you having to worry about anything.
