Tuesday, July 31, 2012

Two Smart ways to optimize your MySQL Database Tables

Large amount of data in MySQL table can affect your whole MySQL database performance. But with just a little work on MySQL table, you can improve your whole database. Here are two maintenance ways to better optimize your table:

1)  By MySQL Queries:

2) By PhpMyAdmin: This is the long procedure & need to explain step by step: When we creted table, there is lot of wasted free space in MySQL tables, To check it first login to phpMyAdmin and select your MySQL database.

You can see in figure:

  • list & type of all the tables in the database
  • Available number of records
  • size and overhead (overhead" means free space which isn't used by table but is taking up disk space.)

If you check second table then you can easily understand that this table needs to be optimize because the table has 687 records but taking much more disk space (533.5 MB). Yo can clearly see that the table isn't optimal so use following steps to optimize this table:

Select the appropriate table by clicking one of the buttons which shows in figure:
(Search, Insert..). The operations tab has different functionality which is not related to this topic.

Now scroll down at the bottom of the page & click "Table maintenance" options just like it:

After that click to "Optimize table" button to optimize your table!!
Congratulations, you have successfully optimize your table!!

No comments:

Post a Comment