Optimize WordPress – How to find the slowest MySQL queries

I’m going to talk a lot about WordPress speed optimization, because it is very important not only to get and keep more visitors but also to get to the top of Google.
You should always test your site speed using the “Google PageSpeed Insights” and then try to resolve any issues and hopefully get a score at least 85/100.

It’s not so easy to get a 100/100 score for a WordPress site, but I will help you get as close to this top result as possible. You can subscribe to my “WP Optimized” newsletter and you will learn some tricks on how to optimize the WordPress core, the theme and the plugins you’re using.

Optimize WordPres for Speed

In this post I’m going to show a technique which should be used mostly by developers, while it need some code changes in the index file of WordPress.
If you’re afraid of doing that then you can skip this post and read my future posts about optimization.
I show this code here just to explain one of the methods I’m using to track the performance of my WordPress sites.

Detecting slow MySQL Queries

To see all the MySQL queries executed per page load on a WordPress site you should put this code in the beginning of the index.php file (in the root folder of your WordPress site), right after the PHP directive (<?php):

define(‘SAVEQUERIES’, true);

And then the following code at the end of the same file:

if (isset($_GET[‘dbg’])) {
   global $wpdb;
   echo “<pre><br />”;
   echo ‘ <strong>’.get_num_queries();
   echo ‘ queries in ‘;
   timer_stop(1);
   echo ‘ seconds.</strong><br /><br />’;
   $i = 1;
   $arr = array();
   foreach ($wpdb->queries as $q) {
      $tm = FormatNum($q[1]);
      $q[1] = $tm;
      $arr[$tm.$i] = $q;
      $i++;
   }
   krsort($arr);
   $i = 1;
   foreach ($arr as $q) {
      echo ”
         <strong>Query $i</strong><br />
         Time: $q[1]<br />
         Query: $q[0]<br />
         Place: $q[2]<br /><br />
      “;
      $i++;
   }
   echo “</pre>”;
}function FormatNum($tm) {
   if (strpos($tm, ‘E-‘)) {
      $stub = explode(‘E-‘, $tm);
      $prefix = str_repeat(‘0’, $stub[1]-1);
      $tm = ‘0.’.$prefix.str_replace(‘.’, ”, $stub[0]);
   }
   return $tm;
}

What this code does is the following – when you load any of your pages and add a $_GET parameter named “dbg” you will see at the bottom of the page a list of all the queries executed in the process of generating the current page.

To add the needed parameter in the URL you should modify it like this:
http://yourdomain.com/blog/path/ -> http://yourdomain.com/blog/path/?dbg=1

OR like this:
http://yourdomain.com/blog/?p=7 -> http://yourdomain.com/blog/?p=7&dbg=1

In this example we’re using a $_GET parameter to activate the output of the queries, but you can use other methods.
For example you can change the “IF” statement like this:
Change this line:

if (isset($_GET[‘dbg’])) {

To this line:

if ( current_user_can( ‘administrator’ ) ) {

In this case the output will be displayed only if you’re logged-in to WordPress with administrator rights.
I personally prefer the first method, without being logged-in, because I will see exactly how it loads for visitors. If you’re logged-in as an admin there will be few extra queries because of the user bar loaded at the top of the page.

When you add the code and load any of your site pages this is the output you should see at the bottom of the page:
SQL Log

The queries are sorted by speed and the slowest are at the top.
If you would like to see the queries in order of execution then you can simply comment the following line in the above code: krsort($arr);

I’m using this method to detect and optimize slow queries.
Also when I’m testing new plugins and themes I run 2 tests – one before the new extension installed and one after that.
This way I can see how many queries this plugin or theme requires and how they affect the overall speed.

In the next weeks I will show you some very useful examples for WordPress optimization and how I used this method to optimize the Eleganth themes and how it helped me decide which social sharing plugin to use on my site.

Subscribe to my “WP Optimized” newsletter and do not miss my optimization tricks.

Posted in WP Optimized

Leave a Reply

Your email address will not be published. Required fields are marked *

*