SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Addict
    Join Date
    Jan 2008
    Location
    Palm Harbor, FL
    Posts
    348
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Unhappy Trouble optimizing all tables in a database

    At the end of a daily cron job, a php script runs the code below to optimize all tables in a database. The script produces no errors, but seems to fail at optimizing the tables; I have noticed that the overhead size of my tables has been increasing.

    PHP Code:
    //OPTIMIZE TABLES
    $all_tables mysql_query("SHOW TABLES") or die(mysql_error());
    $current_table mysql_fetch_assoc($all_tables);
    do {
        
    mysql_query("OPTIMIZE TABLE $current_table") or die(mysql_error());
    } while (
    $current_table mysql_fetch_assoc($all_tables)); 

    Anyone have any ideas?

  2. #2
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,578
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    When you run long queries (OPTIMIZE can take a long time) in a loop like this, you sometimes lose your connection. I'm sure there's some explanation for that, but I've run into it enough with scheduled jobs just like this that I simply don't write code like that anymore.

    Instead, fetch all the table names into an array, then create a separate loop which opens the connection, runs OPTIMIZE [table] and closes the connection each time.

  3. #3
    SitePoint Addict
    Join Date
    Jan 2008
    Location
    Palm Harbor, FL
    Posts
    348
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Dan Grossman View Post
    When you run long queries (OPTIMIZE can take a long time) in a loop like this, you sometimes lose your connection. I'm sure there's some explanation for that, but I've run into it enough with scheduled jobs just like this that I simply don't write code like that anymore.

    Instead, fetch all the table names into an array, then create a separate loop which opens the connection, runs OPTIMIZE [table] and closes the connection each time.
    Alright, thanks, I'll give that a try and see how it goes.

    By the way, the same script deletes outdated records from about a dozen tables before the optimizing. All of those DELETE queries should be fine to run in a single connection, right?


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •