Mysql – Need ideas about OPTIMIZE TABLE

deleteMySQLoptimization

Looking at a database with 10 tables and fairly active at by-the-hour changes. On the first of each month, I purge some rows from 3 tables to remove outdated material and keep the size down. All of these tables show highlighted (red) 'Overhead' in phpMyAdmin.

Given these conditions, should the tables be OPTIMIZED just after the purge. If not a good idea, why? (The purge occurs at lowest usage time of day.)

Let's say the tables and current (mid-month) Data & Index sizes are:

          Rows     Deleted     Data      Index

table1   17,000     7000      4.3 MiB    1.2 MiB    Holds +300/day Transaction Info
table2    6,000     1000     25.5 MiB    231 KiB    Holds User Schedule Info
table3    1,800       30      297 KiB     43 KiB    Holds User Info

This question is in response to learning about indexes and efforts to reduce slow queries and learning about high counts for:

  1. Handler read rnd next
  2. Handler read prev
  3. Created tmp disk tables
  4. Opened tables

In reading articles about these items, it seems to be a 'learned' science and requires testing.

Thanks for responding.

Best Answer

Your tables are incredibly small. You should be able to run

OPTIMIZE TABLE table1, table2, table3;

very quickly.

Looking at the statistics in your question

  • Handler_read_rnd_next : The number of requests to read the next row in the data file. This value is high if you are doing a lot of table scans. Generally this suggests that your tables are not properly indexed or that your queries are not written to take advantage of the indexes you have.
  • Handler_read_prev : The number of requests to read the previous row in key order. This read method is mainly used to optimize ORDER BY ... DESC.
  • Created_tmp_disk_tables : The number of internal on-disk temporary tables created by the server while executing statements. If an internal temporary table is created initially as an in-memory table but becomes too large, MySQL automatically converts it to an on-disk table. The maximum size for in-memory temporary tables is the minimum of the tmp_table_size and max_heap_table_size values. If Created_tmp_disk_tables is large, you may want to increase the tmp_table_size or max_heap_table_size value to lessen the likelihood that internal temporary tables in memory will be converted to on-disk tables. You can compare the number of internal on-disk temporary tables created to the total number of internal temporary tables created by comparing the values of the Created_tmp_disk_tables and Created_tmp_tables variables.
  • Opened_tables : The number of tables that have been opened. If Opened_tables is big, your table_open_cache value is probably too small.

If these global status variables present a problem after running OPTIMIZE TABLE, this is usually a strong indicator that you have to improve the design of your queries and the size of certain caches.

Related Question