How to optimize MySQL databases?

Posted In WordPress - By NitiN Kumar Jain On Tuesday, August 25th, 2009 With 0 Comments






Pin It


Recently while going through a strange blogging phase with slow website response and regular Internal server errors, I raised a query with the DreamHost technical team and I was told that I am over consuming my allotted RAM usage on their shared server which is 150MB. I was told to optimize the MySQL database that I am using which can reduce the load of the blog on the server.

Following below steps I tried to optimize the database to check if it really has any impact and believe me it worked.

You can also have a try. (Ensure to backup your database in case something goes wrong)

  1. Log into the control panel of MySQL databases.
  2. On the first screen after logging in, click Databases.
  3. This will list all the databases you have setup.
  4. Click on the database that you want to optimize.
  5. The database screen will list the tables for the given database.
  6. Either check all tables or only select the tables having overhead. Notice the values in the Overhead column in the following screen shot.

    (Click on the image for a larger and clearer view)

  7. Select Optimize table from the drop down.
  8. Selected tables will be optimized.
  9. Select the table again to cross check as below.

    (Click on the image for a larger and clearer view)

  10. You will notice that overhead for every table has been cleared and total size of the database has also reduced. In my case it got reduced from 11.7 MB to 4.1 MB i.e. almost 65% reduction. Isn’t it great?
  11. That’s all you need to do.

If you are wondering what the Optimize Table command actually does, according to the MySQL Manual it basically defragments your database:

OPTIMIZE TABLE should be used if you have deleted a large part of a table or if you have made many changes to a table with variable-length rows (tables that have VARCHAR, BLOB, or TEXT columns). Deleted records are maintained in a linked list and subsequent INSERT operations reuse old record positions. You can use OPTIMIZE TABLE to reclaim the unused space and to defragment the data file.

In most setups, you need not run OPTIMIZE TABLE at all. Even if you do a lot of updates to variable-length rows, it’s not likely that you need to do this more than once a week or month and only on certain tables.

Do respond if it helps!

-NKJ