2. Subsequent Article: Discover The right way to Uncover Which Are the Slowest Queries
Hiya, welcome to the These days in PHP podcast I am Manuel Lemos from the PHP Courses website. And right this moment, I’ll current to you an episode that I hope it is attention-grabbing to you, which is about MySQL database question optimization.
As you might know, many builders not solely of PHP however many languages, use MySQL SQL question database to retailer details about their purposes and it is all the time attention-grabbing to make that database queries quicker and for that, you must have a great standards not solely to find that are the SQL queries which are definitely worth the effort of optimization and likewise what you are able to do to do a great optimization that basically works nicely.
And ultimately, I’ll present you how you can check that optimization, so you may confirm for your self if the precise optimization that’s recommended actually gives quicker database queries. So let’s transfer on to this episode.
1. Discover Which Database Queries Are Price Optimization
So step one that you must carry out once you execute an effort to optimize your databases is decide what are database the queries which are price of optimization as a result of not all queries are executed as regularly or not all of them are sluggish.
So you must discover the queries which are sluggish and likewise are executed regularly.
2. Test if the MySQL Gradual Question Log Is Enabled
So one useful resource that MySQL database server and different appropriate database servers present is having a sluggish question log. This helps so much find these queries which are price some optimization.
So the very first thing that you must do is to find out that are these sluggish queries. And to attain that you must arrange MySQL database server to allow the sluggish question log. To attain that you could execute a command that I am displaying right here.
sudo mysqld 2>/dev/null --verbose --help | grep slow_query_log
Mainly, it calls them mysqld command which is the server command, at the very least on Linux or different Unix-compatible programs on which MySQL runs.
You may execute this command and use the verbose parameter to output the values of the choices that configure the way in which MySQL server works. Then you definately filter the results of this command to simply get the choices which are related to the sluggish question log.
Right here beneath it is the output of this command and you’ll see the sluggish question log right here beneath, in pink, this selection is ready to false.
So it’s disabled now. You may see additionally the trail to the sluggish question log file which can be an possibility that you could configure.
3. Allow the MySQL Gradual Question Log
So what you must do subsequent is to allow sluggish question log. For that you must edit the my.cnf file of MySQL.
You want additionally to determine the place is that situated. Normally in Linux programs that’s beneath /and so on/my.cnf file. So when you must change that possibility of the sluggish question log you must edit the my.cnf file and set the sluggish question log to 1.
You can even configure the trail of the sluggish question log, as you might even see right here.
You may change it to a path that’s helpful for you. I’ve modified the trail right here simply to indicate that you could change the trail in order for you.
You needn’t change the default path if you happen to do not need to. It is as much as you to resolve that it is an possibility. So let’s transfer on to the following step.
4. Restart the MySQL Server to Make the Configuration Adjustments Take Impact
After you alter these choices, they aren’t taken over within the MySQL server. It’s good to restart MySQL servers.
On this case, I take advantage of MariaDB. You should use another appropriate database server. And I additionally use Linux. Within the case OpenSuSE Linux however so you must run the rcmariadb restart command.
rcmariadb restart
If you happen to use different system you must determine what precisely are the instructions possibly you’ve got a person interface, a graphical person interface to do this. That is good. When you’ve got that. use that. It is as much as you to determine that we’re going to deal with the steps to optimize right here.
5. Take a look at if the Gradual Question Log was Enabled Efficiently
So when you restarted the server, the brand new values are the choices are enabled. So we’ll let’s test to ensure that they actually loaded the brand new possibility values that you simply execute once more the mysqld command. It is the identical command.
sudo mysqld 2>/dev/null --verbose --help | grep slow_query_log
And you may see beneath right here that the sluggish question log is ready to true. So it is enabled. And likewise the brand new path is ready. OK, that is good.
So let’s transfer on subsequent step.
