Loading...
Latest news
COVID19-Effet de loupe et biais de sélection
COVID19 70-80% des morts sont vaccinés
COVID19 Le confinement est inutile
700 Films Gatuits
COVID19 Simulation confinement
Développement-code

MySql optimiser les tables

Jordi -



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';
$p = "MON_PASSWORD";
//
$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";
?>
139 comments
  2. 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.

  8. 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.

  11. 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.

  13. 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!

  16. 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.

  17. 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.

Laisser un commentaire

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