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. curry 6

    I precisely needed to thank you so much again. I am not sure the things I could possibly have gone through without the points revealed by you concerning such a problem. It seemed to be a real depressing matter in my opinion, nevertheless being able to view a professional fashion you treated the issue took me to jump for joy. Extremely happier for your advice and as well , hope that you comprehend what a powerful job that you’re undertaking educating the mediocre ones thru your website. I know that you have never come across any of us.

  2. cap screen

    have already been reading ur blog for a couple of days. really enjoy what you posted. btw i will be doing a report about this topic. do you happen to know any great websites or forums that I can find out more? thanks a lot.

  3. click aqui

    I loved your idea there, I tell you blogs are so exciting sometimes like looking into people’s private life’s and work. Every new remark wonderful in its own right.

  4. vnhoi

    Amazing! Your site has quite a few comment posts. How did you get all of these bloggers to look at your site I’m envious! I’m still studying all about posting articles on the net. I’m going to view pages on your website to get a better understanding how to attract more people. Thank you!

  5. empresa

    That’s some inspirational stuff. Never knew that opinions might be this varied. Thanks for all the enthusiasm to supply such helpful information here.

  6. student grant university scholarships

    However, it is virtually all done with tongues rooted solidly in cheeks, and everyone has absolutely nothing but absolutely love for his or her friendly neighborhood scapegoat. The truth is, he is not just a pushover. He is basically that special variety of person strong enough to take all of that good natured ribbing for exactly what it is.

  7. reformas economicas barcelona

    Took me time to read all the comments, but I really enjoyed the article. It proved to be Very helpful to me and I am sure to all the commenters here It’s always nice when you can not only be informed, but also entertained I’m sure you had fun writing this article.

  8. golden goose

    I simply wanted to thank you so much yet again. I am not sure the things that I could possibly have undertaken without the tricks contributed by you concerning this question. It previously was a frightening matter in my circumstances, nevertheless taking note of this specialized approach you treated the issue took me to cry for contentment. I will be grateful for the work and then hope you comprehend what a great job you are always getting into instructing some other people via a web site. Most likely you haven’t encountered all of us.

Laisser un commentaire

Votre adresse e-mail ne sera pas publiée.