Question:
I work with some huge tables on a system I developed. These tables are constantly being updated and the query sometimes becomes slow. Some of them have more than 100,000 lines.
I would like to know how often I should run Mysql's OPTIMIZE TABLE optimization command to improve performance a bit.
Answer:
It's not all about running OPTIMIZE. Maybe you should start splitting your tables into smaller tables, normalize data, shorten queries, analyze query efficiency (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 what would be interesting would be for you to study how the normalization of a database works (especially leaving it in the normal forms 1FN, 2FN and 3FN, although there are 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 characteristics of your queries) or fixing the database settings. Another thing: is the machine where the database is located not overloaded with other services?
Check out these points and see where you can start.