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. kd 12

    I must show my gratitude for your kind-heartedness for men and women that must have guidance on this important idea. Your real dedication to getting the solution all through turned out to be exceedingly significant and have made guys and women much like me to attain their pursuits. Your own warm and helpful hints and tips indicates a lot a person like me and a whole lot more to my colleagues. Best wishes; from each one of us.

  2. supreme new york

    Thanks a lot for giving everyone remarkably remarkable opportunity to check tips from here. It’s always very great and as well , stuffed with a great time for me personally and my office acquaintances to search the blog not less than 3 times in a week to study the newest guidance you have. And lastly, I’m just at all times amazed with the perfect methods you give. Certain 1 facts in this posting are in fact the most suitable we’ve had.

  3. nike x off white

    I would like to express my admiration for your generosity giving support to women who really need help on the matter. Your real commitment to passing the solution around had become astonishingly advantageous and have truly made women much like me to get to their ambitions. Your informative recommendations means a great deal a person like me and especially to my office colleagues. With thanks; from each one of us.

  4. steph curry shoes

    I want to show my love for your kindness supporting those people that require help on in this subject. Your real commitment to passing the message all-around became quite productive and have without exception permitted men and women just like me to reach their dreams. Your own important tutorial implies a whole lot a person like me and further more to my office workers. Thanks a ton; from everyone of us.

Laisser un commentaire

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