1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Automatic SQL Optimise Query

Discussion in 'Closed Suggestions' started by Shamil, Jul 30, 2010.

  1. Shamil

    Shamil Well-Known Member

    Hi, I think it might be beneficial to add to the cron, the option of an automatic optimise query, to help keep the SQL in good shape. Many forum admins neglect this wonderful feature to essentially "defragment" the overhead on the SQL table.

    Perhaps once or twice a month?
  2. Koleckai

    Koleckai New Member

    Optimize should only be run if you delete a lot of information from your database. It is doubtful that running it on a regular basis will provide any benefit and you should definitely not run it during page loads.
  3. digitalpoint

    digitalpoint Well-Known Member

    Definitely should NOT be automatic since it locks the table being optimized. My post table is 5.7GB... running an optimize on it requires the board be shut off for users.

    If someone *wants* to do it, they can... doesn't need to be done by the forum software itself. They can set up their own cron job to do it.
  4. Tigratrus

    Tigratrus Well-Known Member

    Agreed. But it might be nice to provide an *option* for it (as the OP mentioned) in the Maintenance section, but it would certainly have to be carefully labeled regarding the potential issues. Not a critical first release issue as it's an easy add later, but time permitting it's potentially useful for some.

    Personally I'd just use Navicat to go in and work with the DB directly :).
  5. mlx

    mlx Well-Known Member

    I totally agree. Our post table is about 2 GiB ... so I guess the optimize query would lock the forum for several minutes ... i.e. something like this shouldn't be done automatically.
  6. Stuart Wright

    Stuart Wright Well-Known Member

    I think the philosophy of Kier and Mike has been, where there is a choice but an obviously better option, don't offer the choice. Just do the best option.
  7. Mike

    Mike XenForo Developer Staff Member

    To be honest, if you can afford to do an optimize (because your data is small enough), you don't need to do it. :)
    digitalpoint likes this.
  8. Shamil

    Shamil Well-Known Member

    Is there any performance issue with table with heavy overhead?
  9. Floris

    Floris Guest


    The overhead worry is in regards to tables getting corrupt.

    Running it manually every quarter should be enough for big sites who prune a lot.

Share This Page