Simple analysis and improvement of MySQL schema

Here is some basic analysis and advises on improvement of MySQL database performance. From time to time you should check you MySQL schema status, I use the following request for it:

SELECT                                                                                                                                                                                                                                                                         
    table_name AS table_name,                                                                                                                                                                                                                                                  
    engine,                                                                                                                                                                                                                                                                    
    ROUND(data_length/1024/1024,2) AS total_data_size_mb,                                                                                                                                                                                                                      
    ROUND(index_length/1024/1024,2) AS total_index_size_mb,                                                                                                                                                                                                                    
    ROUND((data_length+index_length)/1024/1024,2) AS total_size_mb,                                                                                                                                                                                                            
    ROUND(data_free/1024/1024,2) AS total_free_size_mb,                                                                                                                                                                                                                        
    ROUND(data_free/(data_length/100)) AS defragment_percent,                                                                                                                                                                                                                  
    table_rows                                                                                                                                                                                                                                                                 
FROM                                                                                                                                                                                                                                                                           
    information_schema.tables                                                                                                                                                                                                                                                  
WHERE                                                                                                                                                                                                                                                                          
    table_schema=DATABASE();

Field defragment_percent value should be 0, but it may be even higher than 100(e.g. you loaded 3MB of data to the table and then deleted 2MB, the table will still be 3MB with real data of 1MB). The most prone to fragmentation are VARCHAR fields so pay attention to the tables where you use VARCHAR a lot. To optimize tables you can use MySQL command:

OPTIMIZE [NO_WRITE_TO_BINLOG | LOCAL] TABLE mi_tabla1 [, mi_tabla2] ...;

To optimize the whole schema you can use:

mysqlcheck -o <databasename>

Field total_index_size_mb should be less than total total_data_size_mb. If it is not so then review your indexes.

Also I'd recommend to use MySQLTuner. MySQLTuner is a script written in Perl that allows you to review a MySQL installation quickly and make adjustments to increase performance and stability. To get it you can use:

wget http://mysqltuner.com/mysqltuner.pl

Then run it with

perl mysqltuner.pl

This tool will provide you with analysis of your MySQL server and some advises on how to improve its performance.