Introduction to MySQL Optimize Table
There is a component in Mysql named query optimizer that optimizes the complex queries and decides the flow of the execution and the index to be used of the tables involved in the query statement to make as much optimization in time and performance as it can be. But sometimes, especially in the case when many indexes are defined on a table or a large number of updations and insertions are made on the table, the query optimizer won’t be able to optimize the query mush and would result in time and space complexities in query execution. Also, when too many data manipulation operations are performed on the table it leads to fragmentation of the data stored at the physical level, and hence fetching the indexed data becomes slower. For this MySQL provides a query statement called OPTIMIZE TABLE.
In this article, we will learn the usage and benefits of running the optimize table statement, its syntax, and example.
Optimization of the table
How the data is stored in the tables on the physical level affects a lot to the indexing and fetching the data from it. When rigorous UPDATE, DELETE, or INSERT operations are performed on the table, there is an occurrence of defragmentation of the data at the physical level. This affects the performance of the application and reports as internally we use the SELECT statement consisting of multiple joins on multiple tables whose indexing and execution flow is decided by the query optimizer of MySQL. This optimizer does not work well with fragmented data. The OPTIMIZE TABLE query defragments the data at the physical level by reorganizing the indexes and the physical storage of the data of the table to improve the efficiency of input/output operations and decrease the space required to store the data.
1. InnoDB storage engine
The actual operations performed on the table depends on the storage engine of the table. In the case of InnoDB tables, when a large number of update, insert or delete operations are performed and that table has .ibd file for itself when innodb_file_per_table option is enabled. The disk space is used by the OS after reclaiming it and the reorganization of the indexes on the table is performed. When a large number of manipulations are done on the table columns that form the part of the FULLTEXT search index for InnoDB tables then we should optimize the table using OPTIMIZE TABLE statement. This increases the speed of FULLTEXT searches made on the table.
2. MyISAM or ARCHIVE storage engines
In case of MyISAM or ARCHIVE tables, when too many DELETE operations are executed or when variable-length rows having datatype VARCHAR, BLOB, TEXT or VARBINARY are changed, you should optimize the tables as it will defragment the data and the linked list that is used to store the records are also restructured enabling usage of unused space. Optimization further leads to improved performance of the query statements executed on the tables.
We require SELECT and INSERT privileges on the table for the execution of the OPTIMIZE TABLE query statement. Note that this statement cannot be used for VIEWs as no physical memory is consumed by VIEWs and they are just the logical entities. We can run this statement on the partitioned tables too. The OPTIMIZE TABLE statements are written to the binary logs by default. This makes sure that they are further replicated to the slaves where the tables are replicated. To skip the logging of the statement we can make the use of NO_WRITE_TO_BINLOG keyword or LOCAL keyword by specifying them in the OPTIMIZE TABLE query.
Syntax
OPTIMIZE [NO_WRITE_TO_BINLOG | LOCAL]
TABLE name_of_table1 [, name_of_table2] ...
The NO_WRITE_TO_BINLOG and LOCAL keywords are optional and are used to avoid logging. We can optimize single or multiple tables at the same time by mentioning them in the comma-separated manner as shown in the syntax.
The output of the OPTIMIZE TABLe query gives the following information –
Table – The name of the table that was optimized.
Op – This is always the optimized value.
Msg_type – This field can include either of this values status, error, info, note, or warning telling the status of execution of the query.
Msg_text – This is the extra and additional description of the executed statement.
Example
For example, we will try to optimize the InnoDB table named developers located inside the database named educba on my MySQL database server. For this, we will make the use of the following query statement –
OPTIMIZE TABLE developers;
The execution of the above query statement gives the following output after the execution –
In this case, the table named developers is rebuilt and the index statistics are updated corresponding to that table. Further, the unused space present in the clustered index is also cleared. The above output says that the table was recreated and the analysis was done by executing the ANALYZE TABLE command internally which gives the output status of the query as OK. The ANALYZE TABLE statement does the necessary analysis for the MySQL query optimizer to perform effectively and accordingly returns the status and related information.
It is very necessary to check, analyze, and optimize the tables of your database if too many operations are performed on it which results in manipulation of the existing data. This helps the database administrators to maintain the database and keep the performance level of the database-based applications up to level.
Conclusion – MySQL Optimize Table
We can make the use of the OPTIMIZE TABLE command provided in MySQL to defragment the data used for storing the table content that is fragmented because of heavy INSERT, UPDATE and DELETE operations on the table. We can optimize more than one table at the same time using the same query by mentioning the list of the names of the tables to be optimized in the comma-separated fashion. The optimization leads to rebuilding of the table and the indexes defined on it which releases the unused memory that can be further used for some other purpose and also leads to faster retrieval of the data from the tables when complex queries involving many queries are used for retrieving the data.
Recommended Articles
This is a guide to MySQL Optimize Table. Here we discuss the introduction to MySQL Optimize Table with appropriate syntax and example. You may also have a look at the following articles to learn more –
