MySql optimiser les tables

Les tables d’une base MySql se fragmentent, surtout celles qui subissent beaucoup de modifications. Comme la fragmentation d’un disque dur, cela provoque des ralentissemnets sur les accés base. Il faut donc optimiser les tables.

Pour réorganiser les lignes fragmentées et éliminer l’espace perdu par les effacements et les modifications de lignes, vous pouvez exécuter l’utilitaire myisamchk en mode de restauration :

shell> myisamchk -r tbl_name

Vous pouvez optimiser une table de la même façon que vous le faîtes avec la commande SQL OPTIMIZE TABLE. OPTIMIZE TABLE effectue une réparation de la table, et une analyse des index, puis trie l’arbre d’index pour accélérer les recherches de clés.

Pour optimiser en automatique vos tables, voici un script PHP que vous pourrez mettre en Cron. Ce script optimise seulement les tables qui en ont besoin. Inutile en effet d’optimiser toutes les tables de vos bases.


< ?php
echo '
' . "\n\n";
set_time_limit( 160 );

$time = microtime();
$time = explode(' ', $time);
$time = $time[1] + $time[0];
$start = $time;

//Connection variables (A MODIFIER):
$h = 'localhost';
$u = 'root';
$dummy_db = 'mysql';

/*The php->mysql API needs to connect to a database even when executing scripts like this.
If you got an error from this(permissions),
just replace this with the name of your database*/

$db_link = mysql_connect($h,$u,$p);

$res = mysql_db_query($dummy_db, 'SHOW DATABASES', $db_link) or die('Could not connect: ' . mysql_error());
echo 'Found '. mysql_num_rows( $res ) . ' databases' . "\n";
$dbs = array();
while ( $rec = mysql_fetch_array($res) )
$dbs [] = $rec [0];

foreach ( $dbs as $db_name )
echo "Database : $db_name \n\n";
$res = mysql_db_query($dummy_db, "SHOW TABLE STATUS FROM `" . $db_name . "`", $db_link) or die('Query : ' . mysql_error());
$to_optimize = array();
while ( $rec = mysql_fetch_array($res) )
if ( $rec['Data_free'] > 0 )
$to_optimize [] = $rec['Name'];
echo $rec['Name'] . ' needs optimization' . "\n";
if ( count ( $to_optimize ) > 0 )
foreach ( $to_optimize as $tbl )
mysql_db_query($db_name, "OPTIMIZE TABLE `" . $tbl ."`", $db_link );

$time = microtime();
$time = explode(' ', $time);
$time = $time[1] + $time[0];
$finish = $time;
$total_time = round(($finish - $start), 6);
echo 'Parsed in ' . $total_time . ' secs' . "\n\n";

  1. kyrie 4

    I would like to convey my affection for your kind-heartedness supporting people that really want help with in this theme. Your special dedication to getting the solution around appeared to be certainly beneficial and has without exception encouraged associates like me to arrive at their dreams. Your valuable tutorial means a lot to me and substantially more to my office colleagues. Many thanks; from everyone of us.

  2. curry 6

    Thanks a lot for giving everyone an exceptionally terrific chance to read from here. It is usually very amazing and also full of a great time for me and my office peers to visit the blog on the least thrice every week to learn the newest secrets you will have. Not to mention, we’re certainly pleased with your powerful inspiring ideas you give. Selected 1 points in this article are in reality the most suitable I have ever had.

  3. yeezy shoes

    My spouse and i got so relieved that Michael could complete his studies through the entire ideas he received when using the web page. It is now and again perplexing to simply always be releasing techniques a number of people could have been trying to sell. We really fully grasp we have the writer to appreciate for this. The most important illustrations you made, the straightforward blog menu, the relationships your site help to promote – it is most overwhelming, and it is leading our son and us do think this concept is satisfying, which is seriously indispensable. Thank you for all the pieces!

  4. supreme

    My wife and i have been peaceful that Raymond could round up his research from the ideas he obtained out of the blog. It’s not at all simplistic to just happen to be handing out instructions which people today have been trying to sell. And now we understand we need you to give thanks to because of that. The type of explanations you made, the straightforward site navigation, the friendships you make it possible to engender – it’s got all remarkable, and it is letting our son in addition to us reason why the situation is amusing, which is seriously pressing. Thank you for the whole thing!

Laisser un commentaire

Votre adresse e-mail ne sera pas publiée. Les champs obligatoires sont indiqués avec *