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:
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 option
innodb_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
ARCHIVEtable, or after making many changes to
ARCHIVEtables with variable size records (tables that have VARCHAR, BLOB, or columns of text). Deleted rows are kept in a chained list, and subsequent
INSERTfaults reuse rows from old rows. You can use
OPTIMIZE 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.
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.