11 {{ upvoteCount | shortNum }}
11 {{ upvoteCount | shortNum }}

Can you 'defragment' a MySQL database ?

by Harvey Segal
7 replies
In the same way that you can defragment a disc to improve
performance is there a way to improve the performance of a
MySQL database by running a utility (as opposed to altering
the application code using it)

Harvey
#database #defragment #mysql
  • Profile picture of the author pjCheviot
    Banned
    Normally known as "optimising", Harvey - and there a few ways (always back-up first!).

    Depends how far you want to go but . . .

    PhpMyAdmin has an "Optimise Table" facility which does what it says on the can.

    Did I mention backing up before attempting anything??
    {{ DiscussionBoard.errors[2281227].message }}
  • Profile picture of the author iBBnet
    I just did this the other night for one of my sites. Just login to phpmyadmin as pjCheviot has mentioned. Once you select the database tables, there's an option to "optimize" your selections.

    As mentioned already, don't forget to backup (your databases) before attempting to do anything with them
    Signature
    Bryan
    After5PC.net
    Freelance Web Development
    and Graphic Design Services
    {{ DiscussionBoard.errors[2281568].message }}
  • Profile picture of the author wsylvester
    Beyond the "optimize" command, you might need to "repair" your table. Or "repair" then "optimize"

    If your performance is slow, it could also be poor design. Which could be fixed by analyzing the queries your application runs and building proper indexes. Which also would not require code changes.
    {{ DiscussionBoard.errors[2282154].message }}
  • Profile picture of the author alexievici
    "Optimize" is not the "magic bullet" to speed up your application, you should only use it if you deleted a large part of your database or if your database gets lots of updates on variable fields (like varchar, text, etc) and if you have a quite large database. First of all make sure it's the queries that are stalling your application and not the scripts, then analyze and optimize the queries.
    {{ DiscussionBoard.errors[2282748].message }}
    • Profile picture of the author Harvey Segal
      Thank you for the replies so far.

      Originally Posted by alexievici View Post

      and if you have a quite large database.
      Would you consider 2.5 million records to be 'large' on a shared host ?


      Harvey
      Signature
      {{ DiscussionBoard.errors[2283586].message }}
      • Profile picture of the author KathyK
        Originally Posted by Harvey.Segal View Post

        Thank you for the replies so far.


        Would you consider 2.5 million records to be 'large' on a shared host ?


        Harvey
        Yes, I would say VERY large - optimising would probably help (backup first, as everyone has said - with that many records, backup often, in fact!). Caching would likely also help.
        Signature

        Cheers,
        Kathy

        {{ DiscussionBoard.errors[2283837].message }}

Trending Topics