Optimize Mysql tables

Question

I work with some huge tables in a system I developed. These tables are constantly being updated and sometimes the query becomes slow. Some of them even have more than 100,000 lines.

I would like to know how often I should run the optimization command OPTIMIZE TABLE Mysql to improve a little performance.

Answer 1

Not everything is about running OPTIMIZE. Maybe you should start dividing your tables into smaller tables, normalize the data, decrease queries, analyze the efficiency of queries (with EXPLAIN), etc.

The first thing I can suggest is to review the queries that are run and see if you need all the data returned. Another possible action is to start purging old data from your table, making it lighter.

Then it would be interesting to study how the normalization of a database works (especially leave it in the normal forms 1FN, 2FN and 3FN, although there is also 4FN, 5FN and Boyce-Codd) and see what is possible to leave in your database.

As for Mysql, you can also see if it’s worth optimizing the tables (for example, for Innodb, but it depends a lot on the characteristic of your queries) or tidy up the database settings. Another thing: will the machine where the database is not overloaded with other services?

Check these points and see where you can start.

Answer 2

According to the Mysql website, I translated what the command says OPTIMIZE TABLE:

Use OPTIMIZE TABLE in these cases, depending on the type of table:

After making a substantial amount of insertions, updates or deletions in a file-based Innodb table .ibd because it was created with the option innodb_file_per_table enabled. The table and indexes are reorganized, and disk space can be recovered for use by the operating system.

After deleting a large part of a table MyISAM or ARCHIVE, or after making many changes in tables MyISAM or ARCHIVE with variable length records (tables that have VARCHAR, BLOB, or text columns). Deleted lines are kept in a chained list and operations INSERT subsequent reuse positions of old lines. You can use OPTIMIZE TABLE to claim unused space and to defragment the data file. After extensive changes to a table, this statement can also improve the performance of instructions that use the table, sometimes significantly.

Website: http://dev.mysql.com/doc/refman/5.1/en/optimize-table.html

In short, OPTIMIZE TABLE only improves the performance of a table if it is modified too often, to reorganize indexes, statistics and repair the table in case of missing or pending records of some update.