How To Optimize MySQL Tables using dbForge Studio for MySQL

What are the main reasons to optimize MySQL tables?

Database tables with frequent CRUD (CREATE, READ, DELETE, UPDATE) operations become slow and corrupted with time. The main reasons are out-of-date key distribution stats, storage fragmentation, and data corruption. Unoptimized database tables result in the following:

• Tables take more space than required to store the current records
• Query execution becomes very slow. Tables can be locked for longer times resulting in decreased response

In these situations, you should try to optimize your database tables in MySQL.

How to find tables for optimization

Though you should periodically optimize all the tables in your MySQL database, if you have to make a choice between the tables to optimize, you can try to see the space that the table has occupied but not utilized to store records. In other words, if a table contains enough unutilized space, you should optimize that table.

Find unutilized storage space in tables

In dbForge Studio for MySQL, you can run SQL commands to find unutilized storage space in tables.

The most commonly used command to find free space in a MySQL table is:

SHOW TABLE STATUS LIKE "tablename"

This statement returns different table properties including the total space occupied by the table (displayed in the Data_length column) and total unused space (displayed in the Data_free column).

As an example, the following screenshot shows the total and free space for a dummy actor table.

If you are only interested in retrieving values from the Data_length and Data_free columns, you can use the script below. The script returns values for all the tables in the sakila database.

dbForge Studio for MySQL contains a tool for table maintenance, which you can use to perform different tasks including table optimization, as you will see in the next section.

Optimize a MySQL table using dbForge Studio for MySQL

The Table Maintenance tool from dbForge Studio for MySQL can be used to perform various optimization tasks as enlisted below:

• Analyze the table
• Optimize the table
• Check table errors
• Checksum
• Repair

There are a couple of methods to access the Table Maintenance tool from within dbForge Studio for MySQL.

Method 1

In the Database menu select Table Maintenance option from the drop-down list as shown in the following screenshot.

The Table Maintenance tool will be displayed as shown in the following screenshot. Here you have to specify the database connection and the name of a database, and select one or more tables to be optimized.

Method 2

Another way to open the Table Maintenance tool is by right-clicking the table name in Database Explorer and then selecting Table Maintenance as shown in the following screenshot.

The Table Maintenance tool will open and the table you right-clicked will be already selected for maintenance, as shown in the following screenshot.

Click Next. You will be transferred to the Options page. Here you need to select the type of the maintenance operation.

Fast way to update key distribution statistics

MySQL query optimizer uses the distribution statistics of keys in the table to create an optimized query plan. If the key distribution statistics has not been analyzed for a long time, the query optimizer has to work with out-of-date key distribution statistics to optimize query plans. This happens when a large number of the UPDATE, DELETE, or INSERT operations occur without the analysis of key distribution statistics. Such query plans are not optimal and may result in delayed queries.

In order to further optimize the performance of you database, use the command that updates statistics about the distribution of values in the table columns and helps the MySQL query optimizer make better decisions about how to execute queries:

ANALYZE TABLE tablename;

The ANALYZE TABLE command from the dbForge Studio's Table Maintenance tool is used to analyze and internally store the key distribution statistics of a table. This helps the query optimizer create optimal query plans resulting in fast and efficient query execution.

To run the command, select Analyze from the Table Maintenance tool and click Execute. The SQL script for the command will be executed and the output will be displayed, as shown below. If the Msg_text column displays OK, it means that the ANALYZE TABLE command has been successfully executed.

Optimize MySQL tables for defragmentation

In a normal file, system installing/uninstalling applications and copying/deleting files results in disk fragmentation. The same happens with database tables if a large number of DML queries (CREATE, INSERT, DELETE) are performed on database tables. Table storage fragmentation severely slows down query performance.

In MySQL, there is acommand that defragments table storage by rearranging data over contiguous data blocks, which improves I/O performance. The statement can only be run on the MyISAM tables:

OPTIMIZE TABLE tablename;

To optimize a MySQL table for defragmentation go to the Table Maintenance tool in dbForge Studio and click Optimize as shown in the screenshot below. Click Execute to run the command.

Depending upon the size of your table and the DML operations performed since the last command, the current OPTIMIZE TABLE command may take some time to execute. Once the command completes execution, you will see the SQL script for it along with its output.

The OK value in the Msg_text column means that the command has been successfully executed.

Check a table for running diagnostic tests

The CHECK TABLE command is used to run diagnostic tests on tables to find errors and report any corruption found. The statement can also be used to find views associated with tables that no longer exist. This command can be used to optimize InnoDB and MyISAM tables in MySQL.

To run diagnostic tests using the command, click Check on the Operation page of the Table Maintenance tool as shown below:

There are five options available for the CHECK TABLE command. Three of them (Quick, Medium, Extended) are available in the form of radio buttons. While two options (Fast, Changed) are available as checkboxes.

Using radio buttons, you can select the following options:

• The Quick option skips checking broken links in table rows to expedite the process
• The Medium option ensures that the deleted links in table rows are valid
• The Extended option runs a full key lookup for all the keys in all the rows of the table. The CHECK TABLE command with the Extended option may take a long time to execute, however it ensures that a table is 100% consistent

Using checkboxes, you can select the following options:

• The Fast option runs diagnostics tests on tables that have not been closed properly. This option can only be applied to MyISAM tables and has no effect on the InnoDB tables
• The Changed option checks table commands that have been changed since the last CHECK TABLE command, or the tables that have not been closed properly

Now, let's examine how this command behaves when it is used outside the graphical user interface:

CHECK TABLE tablename CHANGED QUICK;

To run the CHECK TABLE command, click Execute. Using the script below, we run the command by selecting the Changed and Quick options.

If the statement has been successfully executed, you will see OK in the Msg_text column.

How to run the CHECKSUM TABLE command for MySQL tables

The CHECKSUM TABLE command is used to verify if a table has been modified. The statement calculates a checksum value from table rows, this value can later be matched with the checksum value calculated at a later stage.

For example, you can use CHECKSUM TABLE when you restore a table and see if it has been changed since it was stored last time. Furthermore, the command can also be used to see if the data sent over a network has not been tampered.

To apply the statement, select Checksum from the Table Maintenance tool as shown below and click Execute.

The following SQL script will be executed:

CHECKSUM tablename EXTENDED;

In the output, you will see a BIG INT type value in the Checksum column. This is your Checksum digit.

If you want to check at a later time whether or not a table has been updated, you can again run the CHECKSUM TABLE command. If the checksum value returned by the command is the same as the checksum value returned previously, the table has not been updated. If the table has been updated since the previous command, the old and new checksum values will be different. , the old and new checksum values will be different.

Repair MySQL tables using IDE

The REPAIR TABLE command automatically tries to fix corrupted MyISAM tables. If necessary, it also upgrades tables to remove errors.

To run the command, select Repair on the Operation page of the Table Maintenance tool and click Execute.

If everything goes well, the SQL script for the REPAIR TABLE command will be executed:

REPAIR TABLE tablename QUICK;

As a result, and you will see the OK message in the Msg_text column as shown in the screenshot below.

See how to perform MySQL maintenance with the help of dbForge Studio for MySQL. The Table Maintenance Wizard allows performing the following tasks: Analyze tables, Check tables for errors, Optimize data, Repair corrupted tables.

Conclusion

Using the Table Maintenance tool provided in dbForge Studio for MySQL, you can perform different optimization tasks, such as: analyze, optimize, check, checksum, and repair. With the tool, you don't need to think about whether your database tables are optimized or how to fix them if they are not. Try dbForge Studio for MySQL and see how it can simplify your routine work.