MySql: control and reduce fragmentation of consulting information_schema tables

The fragmentation takes place mainly in tables where there is much movement insert / delete. It grows a lot when the amount of data in the table is very variable in time: for example control tables transactions, user logins, Intermediate table, etc. The first symptom of fragmentation would slow in the consultations, particularly noticeable on tables with many records. For information about the hearing can INFORMATION_SCHEMA.TABLES consultation where we can quickly see the status of the tables and some interesting facts about them.

Index it'll fragmentation of the relationship between the space freed (data_free) for recordsremoved for total board (data_lenght), we will share and order of most to least:

 mysql> SELECT TABLE_SCHEMA, TABLE_NAME, Data_length, data_free (data_free / Data_length) * 100 frag_percent, engine                 
      from INFORMATION_SCHEMA.TABLES                 
      WHERE TABLE_SCHEMA NOT IN ('information_schema', 'mysql') and data_free> 0                 
      frag_percent order by desc;

+--------------+-------------------------+-------- -----+-----------+--------------+--------+
| TABLE_SCHEMA | table_name | Data_length | data_free | frag_percent | engine |
+--------------+-------------------------+-------- -----+-----------+--------------+--------+
| Db1 | alumnos_logueados | 143484340 | 136095052 | 94.8501 | MyISAM |
| Db1 | incidencias_puntualidad | 131917692 | 112362116 | 85.1759 | MyISAM |
| Db1 | incidencias_asistencia | 7901568 | 6265364 | 79.2927 | MyISAM |
| Db1 | usuarios_logueados | 1813192 | 1004284 | 55.3876 | MyISAM |  

With data from this consultation we will find fragmented tables available free space.To defragment just run the OPTIMIZE TABLE statement table. The only snag is that locks the table we do so we must choose a good time to do so. Fragmentation is not that we have to lose sleep because the improvement in 90% of cases will be negligible but neither is more control from time to time.Even in new databases helps us learn to control the boards with more movement.