Find MySQL Slow Queries by Activating the Slow Query Log - 7 minutes - Lately in PHP Podcast Episode 93 Part 1

How to Improve the Speed of An Application that Uses a MySQL Database Server

2. Next Article: Find How to Discover Which Are the Slowest Queries

Listen or download the podcast, RSS feed and subscribe in iTunes

Watch the podcast video, subscribe to the podcast YouTube channel

What was said in the podcast

Show notes

Listen or download the podcast, RSS feed and subscribe in iTunes

Hello, welcome to the Lately in PHP podcast I'm Manuel Lemos from the PHP Classes site. And today, I'm going to present to you an episode that I hope it's interesting to you, which is about MySQL database query optimization.

As you may know, many developers not only of PHP but many languages, use MySQL SQL query database to store information about their applications and it's always interesting to make that database queries faster and for that, you need to have a good criteria not only to discover which are the SQL queries that are worth the effort of optimization and also what you can do to do a good optimization that really works well.

And in the end, I'm going to show you how to test that optimization, so you can verify for yourself if the actual optimization that is suggested really provides faster database queries. So let's move on to this episode.

1. Find Which Database Queries Are Worth Optimization

So the first step that you need to perform when you execute an effort to optimize your databases is determine what are database the queries that are worth of optimization because not all queries are executed as frequently or not all of them are slow.

So you need to find the queries that are slow and also are executed frequently.

2. Check if the MySQL Slow Query Log Is Enabled

So one resource that MySQL database server and other compatible database servers provide is having a slow query log. This helps a lot in finding those queries that are worth some optimization.

So the first thing that you need to do is to determine which are those slow queries. And to achieve that you need to set up MySQL database server to enable the slow query log. To achieve that you can execute a command that I'm showing here.

sudo mysqld 2>/dev/null --verbose --help | grep slow_query_log

Basically, it calls them mysqld command which is the server command,  at least on Linux or other Unix-compatible systems on which MySQL runs.

You can execute this command and use the verbose parameter to output the values of the options that configure the way MySQL server works. Then you filter the result of this command to just get the options that are relevant to the slow query log.

Here below it's the output of this command and you can see the slow query log here below, in red, this option is set to false.

So it is disabled now. You can see also the path to the slow query log file which is also an option that you can configure.

3. Enable the MySQL Slow Query Log

So what you need to do next is to enable slow query log. For that you need to edit the my.cnf file of MySQL.

You need also to figure out where is that located. Usually in Linux systems that is under /etc/my.cnf file. So when you need to change that option of the slow query log you need to edit the my.cnf file and set the slow query log to 1.

You can also configure the path of the slow query log, as you may see here.

You can change it to a path that is useful for you. I have changed the path here just to show that you can change the path if you want.

You don't need to change the default path if you don't want to. It's up to you to decide that it's an option. So let's move on to the next step.

4. Restart the MySQL Server to Make the Configuration Changes Take Effect

After you change those options, they are not taken over in the MySQL server. You need to restart MySQL servers.

In this case, I use MariaDB. You can use some other compatible database server. And I also use Linux. In the case OpenSuSE Linux but so you need to run the rcmariadb restart command.

rcmariadb restart

If you use other system you need to figure what exactly are the commands maybe you have a user interface, a graphical user interface to do that. That's nice. If you have that. use that. It's up to you to figure that we are going to focus on the steps to optimize here.

5. Test if the Slow Query Log was Enabled Successfully

So once you restarted the server, the new values are the options are enabled. So we'll let's check to make sure that they really loaded the new option values that you execute again the mysqld command. It's the same command.

sudo mysqld 2>/dev/null --verbose --help | grep slow_query_log

And you can see below here that the slow query log is set to true. So it's enabled. And also the new path is set. OK, this is good.

So let's move on next step.