I work with some huge tables in a system I've developed. These tables are constantly being updated and sometimes the query becomes slow. Some of them have more than 100,000 lines.
I would like to know how often I should run the optimization command OPTIMIZE TABLE of Mysql to improve performance a bit.
It's not all about running OPTIMIZE. Maybe you should start splitting your tables into smaller tables, normalizing the data, decreasing the queries, analyzing the efficiency of the queries (with EXPLAIN), etc.
The first thing I can suggest is to review the queries that are rotated and see if you need all the data returned. Another possible action is to begin purging old data from your table, leaving it lighter.
Then the interesting thing would be you study how normalization of a database works (especially leaving it in normal forms 1FN, 2FN and 3FN, although there is also 4FN, 5FN and Boyce-Codd) and see what it is possible to leave in your database.
As for MySQL, you can also see if it is worth optimizing the tables (for example, for InnoDB, but depends a lot on the characteristics of your queries) or tidy up the database settings. Another thing: is the machine where the database is not overloaded with other services?
Check out these points and see where to start.
According to the MySQL site, I translated what the OPTIMIZE TABLE command says:
Use
OPTIMIZE TABLEin these cases, depending on the type of table:After doing a substantial amount of insertions, updates, or deletions in an InnoDB table based on a
.ibdfile because it was created with the optioninnodb_file_per_tableenabled. The table and indexes are rearranged, and disk space can be retrieved for use by the operating system.After deleting a large part of a
MyISAMorARCHIVEtable, or after making many changes toMyISAMorARCHIVEtables with variable size records (tables that have VARCHAR, BLOB, or columns of text). Deleted rows are kept in a chained list, and subsequentINSERTfaults reuse rows from old rows. You can useOPTIMIZE TABLEto reclaim unused space and to defragment the data file. After extensive changes to a table, this statement can also improve the performance of statements that use the table, sometimes significantly.
Website: link
In summary, 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 for some update.
