If you are running your database on MySQL, it’s crucial to analyze and optimize your MySQL table once in a while. This guide will cover why it’s important to optimize database tables. Thereafter, we will see two main methods to run MySQL analysis table query. It’s standard practice to analyze the tables before running MySQL optimize table.
Why Optimize tables in MySQL?
The main work of MySQL table is to perform the CRUD operations which stands for Create, Read, Update, and Delete.
A database is used whenever a user requests data on the website. The code checks the database before showing the results. When you do MySQL optimize table task, you will get a cleaner and better database. This helps you with the following.
- It helps you to execute the queries at a higher speed.
- The total size of the database decreases.
- You will get a cleaner database.
There are two main ways you can clean a database and optimize it. The traditional method is the command line method where you can enter the commands to optimize the tables. However, if you are looking for advanced methods, you can always use dbForge Studio. Let’s start with how to use the command line for optimization. Later we will see more about dbForge.
Use Command Line to Optimize Tables
There are two main procedures we need to follow. First, we will have to determine which tables need optimization. Thereafter, we can move to optimization.
The table that contains more data and consumes more space needs to be optimized first.
So, you can connect to the database by entering:
mysql> use <database_name>
Next, you can see the status of the table.
mysql > show table status like “<table name>” \G
It will show a few values. Data_length is the total space used by the table. Data_free shows the unallocated space for each table.
MySQL allocated a certain space for each table. Therefore, data_free allows you to determine where you need to run MySQL optimize table operation.
You can go a little in-depth by entering:
mysql> select table_name, data_length, data_free
from information_schema.tables
where table_schema='<schema name>’
order by data_free desc;
It will be a lengthy size as it’s shown in bytes. So, before we optimize database tables, we will optimize the above command. You can always change the length by changing 1024. Make sure that you replace the schema name with the actual schema name.
mysql> select table_name, round(data_length/1024/1024), round(data_free/1024/1024)
from information_schema.tables
where table_schema='<schema name>’
order by data_free desc;
Now that you have an idea of which tables need to be optimized, you can run the command to optimize the table.
mysql> optimize table <table_name>;
The above command will start optimizing the table. If you want to optimize multiple tables, you can enter the names followed by commas.
mysql> optimize table <table_name1>, <table_name2>;
Use GUI tool to Optimize MySQL Table
The tool we are going to use to optimize database tables is dbForge Studio for MySQL.
Before we see the GUI tutorials, it’s important to know that you can always use the MySQL analyze table commands to see the details.
However, as it’s a GUI tool, we will do this operation using a graphical interface.
Step 1: Analyzing the Table
To analyze your table, you will have to the database menu and click on “Table maintenance”. You can then select the table you want to analyze. You can also select multiple tables.
If you don’t want to do that, you can also right-click on your preferred table (the tables are shown in the left panel), and click on table maintenance.
After you select the tables, you will have to select the “Analyze” option. (Make sure you are at the “Operations” menu from the left panel.)
You can then click on “Next” and it will execute the analyze command itself. After the execution, you can see the details in the bottom column. It will show the OK message in the “Msg_text” column.
Step 2: MySQL Optimize Table Operation
The next part is where we will optimize MySQL table. To optimize database tables, the core options are the same. You can head over to table maintenance and then select “optimize”.
So, firstly, you can click on table maintenance from menu > database or by right-clicking on your preferred table.
Now, in the operations tab, you will have to click on Optimize option. While running MySQL optimize table, you can also choose if you want to write on Binlog file (local) or if you want to optimize the table for FULLTEXT only. If you select the full-text option, you can enter the number of words to optimize per run.
Thereafter, you can hit “Execute”. After the process is completed, you can always confirm it by checking the “Msg_text” option.
Step 3: Advanced Options
As you might have seen, the operations also include check, checksum, and repair options. You can always use the options when needed. There are a few other advanced options you can tick.
The tool is not just for to MySQL optimize tables, you can also use it for managing your database in a better way. If you are in the development phase, you will need the repair option. It will repair a corrupted table.
Summary
To conclude, it’s important to optimize your databases once in a while if you want to ensure the performance of your database. It’s always a better option to use the GUI tool instead of using the command line interface. Always remember to run the MySQL analyze table operation before you run MySQL optimize table.
The GUI tool, dbForge Studio for MySQL contains a lot of features to optimize your databases and manage databases more effectively. You can also copy, sync, compare, and visually see the database.
Leave a Reply