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";
?>
326 comments
  1. reformas en general

    If most people wrote about this subject with the eloquence that you just did, I’m sure people would do much more than just read, they act. Great stuff here. Please keep it up.

  4. bape shoes

    Thanks for all of your work on this site. Betty really likes carrying out internet research and it’s simple to grasp why. Almost all notice all regarding the dynamic mode you present worthwhile thoughts via the blog and as well encourage contribution from others about this area of interest then my princess has been studying a whole lot. Enjoy the rest of the year. You’re carrying out a powerful job.

  7. surgical tech certification programs florida

    Dignity College of Healthcare online surgical technician training is far superior to other online Surgical Technician programs because it includes the training, exam review and national certification exams. Register with Confidence and attend a nationally accredited, but affordable program. In just 4 months, you can complete the surgical technician program from the comfort of your home without a loan on your neck. Enroll now at https://dignitycollegeofhealthcare.com/surgical-technician

  9. jordan sneakers

    I precisely wished to thank you so much yet again. I do not know the things I would’ve created in the absence of the type of creative ideas provided by you on my subject. It had become a alarming problem in my opinion, but noticing the very well-written manner you handled that took me to cry over gladness. Extremely happier for this service and as well , sincerely hope you know what a powerful job that you are accomplishing training people today through a web site. I am certain you have never met any of us.

  12. golden goose sneakers

    Thank you for your own work on this site. My daughter loves going through investigations and it’s easy to see why. A lot of people hear all relating to the lively manner you offer helpful things on the web site and as well as cause contribution from visitors about this issue so my girl is without a doubt discovering a lot of things. Enjoy the remaining portion of the year. You are carrying out a glorious job.

  15. bape clothing

    I would like to express appreciation to you just for rescuing me from this dilemma. As a result of researching through the search engines and finding solutions which are not helpful, I thought my entire life was over. Living without the presence of approaches to the difficulties you have sorted out all through your short post is a critical case, as well as the ones that would have adversely damaged my entire career if I hadn’t noticed your blog. Your main natural talent and kindness in touching every aspect was crucial. I am not sure what I would have done if I had not discovered such a step like this. I can also at this point look ahead to my future. Thanks very much for this high quality and effective guide. I won’t hesitate to suggest the website to any individual who needs care about this subject matter.

  17. yeezy gap

    I have to point out my passion for your generosity for men who require help with the study. Your real dedication to passing the solution all over became remarkably powerful and has regularly empowered some individuals just like me to achieve their endeavors. Your invaluable suggestions denotes a lot to me and a whole lot more to my colleagues. Thanks a ton; from all of us.

  19. cortexi

    A friend of mine advised this site. And yes. it has some useful pieces of info and I enjoyed scaning it. Therefore i would love to drop you a quick note to express my thank. Take care

  20. fire watch guards for business

    Our fire watch guards are put through rigorous training to handle any job assigned to them. Our fire watch guards are CPR/AED/First Aid certified. We provide professional fire watch solutions customized to fit each individual client and business. Call now to get started. We’re available 24/7 in all 50 states. We service private clients, small businesses and large corporations. Contact us now to request more information about our fire watch services. https://firewatchguards.com

  21. online acting classes

    The Playground is a young actor’s conservatory: a place where actors are immersed in the craft of Television and Film Acting. Young people of all ages look forward to coming here and taking part in our carefully developed curriculum, a curriculum that has been personally designed by Gary Spatz. Gary Spatz, the founder of The Playground, is one of the top child acting coaches in the world and has 25 years of experience working with children in the entertainment industry. Projects include: The Mickey Mouse Club, Suite Life, Roseanne, Everybody Loves Raymond and many more. Gary is sought after to work with young actors for film and television projects. He has worked with many of the most successful young performers in Hollywood from Britney, Christina and Justin to Dylan and Cole! https://theplayground.com

  22. Best Fire Watch Guards

    At XPressGuards, we take fire prevention seriously. Hiring a fire watch guard can save your life and the financial loss that results from damaged property. Keep in mind that in case of a massive fire outbreak, a fire extinguisher won’t do any good. While you may be able to restore the damaged property and cover the financial loss, you can’t put a price on life. Our fire watch guards will alert the fire department upon the first sign of something suspicious thus protecting your employees as well as your property. Contact us now for more information. https://xpressguards.com/fire-watch-security-guards/

  23. reverse mortgage loans

    Are you looking to purchase a new property and need to secure a home loan? Are you thinking about refinancing your property to take cash out or get a better interest rate? Look no further, we’ve got you covered. If you’re looking to renovate your property and/or need repairs, we can take care of that for you as well. We’re your all-in-one real estate concierge. Get everything you need done for your property with Estate Solutions. Contact us for more information now. https://estatesolutions.llc

  25. Headhunter Game

    The on the web cooking games are surprisingly straightforward, but
    a lot of enjoyable. What is even much a lot more fascinating to note is the fact that
    these Dress up and cooking games are continually becoming updated by their
    builders. Step Three: Just for fun, keep records of how
    much of each item you were able to stack. Anything you can stack is fair game.

    How many pinecones can you stack without watching them fall?
    But there are three to five tree bases, and each runner can only
    claim sanctuary twice at each tree before they’re left with no choice but
    to run until they’re caught. Step Three: Now give each runner a different colored
    marker tied to a string around their neck. Now you can play from end-to-end.
    Now it’s also a game of observation. Learn how to play this backyard game on the next page.
    Find out how to play Tree Tag on the next page. Tag turns over a
    new « leaf » with this crazy adaptation. Ready to skip over to four?
    Attach them with packing tape so you don’t hurt the tree and so
    it can be easily removed when the game is over.

Laisser un commentaire

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