The other day a random thought sort of popped in my head. Yes, it happens and I have a good laugh about it too. The random thought was why queries can be slow and how to find them. Now, there are several resources out there and let’s be honest here it could be a little tough to know which one to use. No lie, it won’t matter much as long as you know what to look for in the end. I say this because it really did take me some time to actually figure out how to read a query.
It’s not super easy the first time around.
So not so much reading a query but rather optimizing one. One of the key takeaways from my learning was to have some index. That will often times increase the performance of the application. This was one of the things I often saw with some WooCommerce sites and some translation plugins. They can do some pretty heavy queries.
Heavy?
Not quite like the Marty McFly heavy but possibly close. This one could also be one of those where it might be a little subjective because what one considers heavy others may consider a light load. I’m sure some are probably thinking or even already asking what makes for heavy? The time. This is part of the reason I say it is a little subjective. By default you can log queries that are ten seconds or longer.
Ten.
While some say it’s a good threshold there are some who might say that’s still too long. There are different applications so this is part of that reasoning as well. In a perfect world those would take less than a second.
The super cool part here is that it can be configured. Depending how snappy you want things you can bring that down to one second but let’s be honest here and use 5 seconds. Best case we don’t have any that run longer than that and worst case we bring that down.
But How?
Ideally you’d want to do this before your application is even shipped or deployed to the world. Again, often times that’s not what happens. Especially when you have a tight deadline to meet or when you are on shared hosting. That is going to probably be the more common scenario when it comes to that as well. That’s the case for me and this site. I don’t have ability to change database configuration. There is however a decent way of logging the queries.
Using WordPress hooks and filters is, of course, going to be the better way here. We will want to hook to the shutdown hook and then run our logic. Quick and dirty example of the usage:
<?php // Be sure to change things accordingly
add_action( 'shutdown', function() {
global $wpdb;
$log_file = fopen( ABSPATH . '/wp-content/slow-log.txt', 'a' );
foreach( $wpdb->queries as $q ) {
if ( $q[1] > 3 ) {
fwrite( $log_file, "/===== slow query ===/\n\n" . date( "F j, Y, g:i:s a" ) . "\n" );
fwrite( $log_file, $q[0] . " - ($q[1] s)" . "\n\n" );
}
}
fclose( $log_file );
});
That will log queries that take 3 or more seconds to a file called slow-log.txt inside of the wp-content folder. You can of course change that to what may better suit your needs but that will at the very least give you an idea of what could be causing some slowness as well when it comes to database performance. This is really perfect for some plugin debugging as well.
Now, the tricky part here is actually getting meaningful information out of it, right? Because simply getting the queries isn’t enough. We need to know how to better those. This is where the EXPLAIN command comes in super handy. Granted it is only really handy when you are connected to the database. You could potentially connect to it and then run the query with EXPLAIN before the query and that will provide information. To really know more I highly suggest reading the documentation on the output which can be found on their site.
That output
That output will give you a better idea of what to index so the query can be more performant. Again, I really cannot emphasize that reading the documentation will greatly help out in determining what should be the best steps forward.
The super cool part is that the tutorial on the MySQL site does a really good job at providing repeatable steps for analyzing queries in order to get better performance. This isn’t by any means the ultimate guide to database performance but it can improve it. The reason I say that is because not all sites will be using the same plugins with the same hosts or even settings and configurations.
Learning all of this was fun for me and it did take some time, I don’t expect people to pick it up either on the first try so the key being, practice.