EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Data Science Data Science Tutorials MySQL Tutorial MySQL Optimize Table
 

MySQL Optimize Table

Payal Udhani
Article byPayal Udhani
EDUCBA
Reviewed byRavi Rathore

Updated June 6, 2023

MySQL Optimize Table

 

 

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 possible. But sometimes, especially when many indexes are defined on a table or many updations and insertions are made, the query optimizer won’t be able to optimize the query mush. It would result in time and space complexities in query execution. Excessive data manipulation operations on a table result in fragmentation of the data stored at the physical level. Consequently, the retrieval of indexed data becomes slower due to this fragmentation. For this, MySQL provides a query statement called OPTIMIZE TABLE.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

This article will teach the usage and benefits of running the optimized table statement, its syntax, and its example.

Optimization of the table

Data storage in tables at the physical level significantly impacts indexing and retrieval. Rigorous UPDATE, DELETE, or INSERT operations performed on the table cause data fragmentation at the physical level. This affects the application’s performance and reports internally; We utilize the SELECT statement that incorporates multiple joins on multiple tables. The query optimizer of MySQL determines the indexing and execution flow of the query. 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 table data 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 depend on the table’s storage engine. When working with InnoDB tables, enabling the innodb_file_per_table option creates a dedicated .ibd file for each table. After reclaiming the disk space, the operating system utilizes this file and triggers the reorganization of indexes on the table. When performing extensive manipulations on the table columns that are part of the FULLTEXT search index for InnoDB tables, optimizing the table using the OPTIMIZE TABLE statement is advisable. This increases the speed of FULLTEXT searches made on the table.

2. MyISAM or ARCHIVE storage engines

Optimizing MyISAM or ARCHIVE tables becomes necessary when excessive DELETE operations or modifications to variable-length rows with datatypes such as VARCHAR, BLOB, TEXT, or VARBINARY. This optimization process defragments the data and restructures the linked list for storing records, allowing for efficiently utilizing 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 VIEWs consume no physical memory and are just logical entities. We can run this statement on the partitioned tables too. By default, the OPTIMIZE TABLE statements write to the binary logs, ensuring their replication to the slaves where the replicated tables reside. To skip the logging of the statement, we can use the NO_WRITE_TO_BINLOG or LOCAL keywords by specifying them in the OPTIMIZE TABLE query.

Syntax

OPTIMIZE [NO_WRITE_TO_BINLOG | LOCAL]
TABLE name_of_table1 [, name_of_table2] ...

You can optionally use the NO_WRITE_TO_BINLOG and LOCAL keywords to avoid logging. We can optimize single or multiple tables simultaneously by mentioning them in a comma-separated manner, as shown in the syntax.

The output of the OPTIMIZE TABLe query gives the following information –

  • Table – The optimized table’s name.
  • Op – This is always the optimized value.
  • Msg_type – This field can include either of these 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 inside the educba database on my MySQL database server. For this, we will make use of the following query statement –

OPTIMIZE TABLE developers;

The execution of the above query statement results in the following output.

mysql optimize table output

In this scenario, the table named “developers” undergoes a rebuild, and the index statistics pertaining to that table are updated. Additionally, any unused space within the clustered index is cleared. The resulting output indicates that the table was recreated, and an internal execution of the ANALYZE TABLE command provides the query status as OK. The ANALYZE TABLE statement does the necessary analysis for the MySQL query optimizer to perform effectively and returns the status and related information accordingly.

It is essential to check, analyze, and optimize the tables of your database if too many operations are performed on it, which results in the 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 use the OPTIMIZE TABLE command provided in MySQL to defragment the data used for storing the fragmented table content because of heavy INSERT, UPDATE, and DELETE operations on the table. We can optimize multiple tables simultaneously by specifying their names in a comma-separated list within the same query. This optimization process involves rebuilding the tables and their corresponding indexes, releasing unused memory. Utilizing the freed memory for other purposes and enhancing the retrieval speed of data from the tables is possible after optimization. This improvement is particularly noticeable when executing complex queries that involve multiple joins.

Recommended Articles

We hope that this EDUCBA information on “MySQL Optimize Table” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

  1. MySQL Cluster
  2. Create MySQL Users
  3. MySQL REINDEX
  4. MySQL REPLACE

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA Login

Forgot Password?

🚀 Limited Time Offer! - 🎁 ENROLL NOW