How to optimize a MySQL database

One of the most important prerequisites for achieving optimal MySQL database performance is indexing. Indexing is an internal MySQL feature that allows faster gathering of data.

Let's take an example table called "sample" with only two rows - "number" and "employee". If you run a simple query such as:

MySQL will check all records and will return only the one that has its number value set to 4.

But if you have several thousand entries for example, this will be a slow query. In this case we have a unique field - "number". Therefore, we can create an index for it. Indexing will create an internal register that is saved in by the MySQL service. It can be done with the following query:

Once this index is set, next time you want to get the information for employee number 4, the service will go directly to it using the index and will return the information much faster.

This is just a very basic example. For bigger databases, the difference in the loading time can be significant. Indexing your database can drastically decrease the loading time of your web applications.

There is another query that you can use to increase the loading speed of your database:

You can use the following command to optimize/repair a database through SSH:

mysqlcheck -orp -u user_name database_name


-o - stands for optimize
-r - stands for repair
-p - when this option is used with the command you will be prompted to enter the password of the database username user_name
-u - the username assigned to the database; in this case you can use your cPanel username with its corresponding password

Alternatively, you can use the detailed instructions in this article:

How to Optimize a MySQL Database with phpMyAdmin

There are other methods which can be used for MySQL optimization such as using of persistent connections, query caching, etc. However, they may require some fine tuning from the Apache and MySQL configuration files.

After the optimization, the MySQL service will take much less time to search in your database which will result in better performance of your scripts.

cPanel is easy to work with when you have the right host to support you. If you need a reliable partner to help you manage your website with cPanel, check out our cPanel hosting services!


  1. Reply May 26, 2015 / 11:36 Black Lion Web DesignSiteGround Team

    Hi! Does SiteGround perform this optimisation for its customers?

    • Reply May 26, 2015 / 12:44 Hristo PandjarovSiteGround Team

      We don't do such actions on a regular basis. However, you can use phpMyAdmin to do it whenever you want to 🙂

  2. Reply December 31, 2015 / 23:45 dkSiteGround Team

    Are there any plugins that do this you would recommend?

    • Reply January 4, 2016 / 10:39 Ivan YordanovSiteGround Team

      There are some plugins/extensions for the different applications that have this functionality. Depending on the application you are using, you can search in its official plugins directory for database optimization tools and choose one of the results.

  3. Reply January 19, 2016 / 19:02 JasonSiteGround Team

    Does wp-optimize plugin work well with siteground hosting? I have it installed on a site now, and for some reason, it does not appear to be calculating what needs optimization. Even after running it, I still see the same DB size. Any advice is appreciated! Thanks!

    • Reply January 20, 2016 / 08:08 Hristo PandjarovSiteGround Team

      Yes, it does, I've used it before on my personal website and many customers use it. Note that if your database is already optimized there won't be a noticeable difference in the database size. However, try disabling our caching when you are performing those tests just to be sure it's not fetching any cached content.

  4. Reply July 15, 2016 / 05:47 Mohsin AliSiteGround Team

    Hi, I want to optimize my client website ( Its PageSpeed Score (77%) on GTmatrix, that is very low. I want to optimize it so that its speed will go above 90%. It is based on WordPress CMS and using Blanco theme. I have applied your above-mentioned method but it not gives me desire result. Can you guide me any other method to improve its speed? OR if I suggest them to use your hosting then does you provide any tools to speed up websites.
    Early reply is most beneficial for me. Thanks.!

    • Reply July 15, 2016 / 08:22 Marina YordanovaSiteGround Team

      Hello, check out this tutorial

  5. Reply September 30, 2016 / 02:12 Sandesh AwalSiteGround Team

    Hello, I am creating a guest house website and i want to add booking form for the customer with check-in, check-out date, number of adults and children. Is there any plugin that helps me easily to create my own booking form. And also how can i easily add multiple javascript and css in my website.

    • Reply October 4, 2016 / 13:30 Ivan StefanovSiteGround Team

      Sandesh, the plugin depends of the application that you've chosen to built your website on. I recommend you to research in application forums and/or Google for recommendations regarding this matter.

  6. Reply October 5, 2016 / 11:51 johanSiteGround Team

    Hello, this is website very usefull and learning

* (Required)