How to use the MySQL Optimize tool

Knowledgebase Article

How to use the MySQL Optimize tool

What is MySQL Optimize?

MySQL Optimize is a tool that allows you to view resource-intensive database queries as well as cache them. This is done using KualoSQL, a special type of SQL service that can log query statistics, and cache queries that you desire.

How to use MySQL Optimize

This demo assumes you've already logged in to cPanel.

1) In cPanel, scroll down to the METRICS subheader and click the MySQL Optimize icon:

MySQL Optimize icon

2) To start using MySQL Optimize with your website, you will need to click Enable next to the MySQL user in the list:

MySQL Optimize user list

3) The website's database configuration file will then need to be edited so that the database connection goes through KualoSQL.

You can edit your configuration file using the File Manager tool in cPanel.

In this example we will configure a WordPress website. That is done by locating and editing the wp-config.php file.

The database host needs to be 127.0.0.1, and the database port needs to be 3308.

Depending on the application type, it may not have a port defined yet, but you can change it for WordPress as follows:

/** MySQL hostname */

define('DB_HOST', '127.0.0.1:3308');

Save the file and then browse a few pages on your website to test.

4) Now that your website is using a database connection through KualoSQL, you can scroll down the MySQL Optimize page and click Analyze next to the database:

MySQL Optimize analyze button

That will compile your website's database query statistics for your review and to allow you to cache any you desire.

The Query Statistics tab will open by default and show the top 10 queries that took the longest total time to run:

MySQL Optimize query statistics

You can click on Runs, Total Time, or Average Time in the table header to sort the queries. The time is shown in milliseconds.

To cache a query, fill in the desired time (in milliseconds) in the TTL (Time To Live) field and click Cache.

Warning: A query's cache does not refresh or become invalidated after database changes. It will only refresh when the TTL expires. This means if you have a long TTL of 3600 (1 hour) and you make a change to the website that is stored in the database and retrieved using the query you cached, it will not show the new change on the website until 1 hour passes and the cache expires and is regenerated.

To avoid old data showing on the website, use caution when catching queries and set appropriate TTL values so the data is refreshed as often as needed. If a query is responsible for pulling data that needs to be very accurate, such as showing an updated number of items in a shopping cart, it should not be cached.

Besides caching queries, this section can be very helpful for determining long-running queries that can be optimized in your scripts. Queries that run for longer periods are highlighted in yellow or red and show a warning message.

The Cached Queries tab will show you any queries you have previously cached:

MySQL Optimize cached queries

You can use the Change TTL button to make the cached query expire at a quicker or slower interval or remove it entirely with the Remove from Cache button.

5) If you decide to discontinue use of MySQL Optimize, you can do so by first editing your application's configuration file to undo the change done in step 3. Next, you can click Disable next to the MySQL user in the list.

And that's it! You now know how to enable and use MySQL Optimize, as well as how to disable it anytime.

Note: Statistics shown in MySQL Optimize are cleared once daily at midnight server time.