In order to diagnose slow SQL Queries on your WordPress site you can either
- print out the queries on a page by page basis (by altering some code in your site files) or
- use a server app (called PHP X-RAY) to identify the points of congestion.
- From there you can make code changes to alleviate the offending code by either overriding your plugin files or theme files.
Table of Contents
Why would you need to debug SQL Queries in WordPress?
Whenever I have had to debug WordPress SQL Queries being made on a page it is usually because one or multiple of the calls are so long that the page can’t load and WordPress shows an error to the user saying “WordPress could not connect to the Database“.
PROTIP: Slow queries can also be the culprit when the CPU on your server has been maxed out. This can sometimes lead to very slow page load times for users if the CPU is maxed out for extended periods of time so the methods discussed below can also help debug why your server CPU is being used intensively.
Basically, whats happening when this is seen is that the calls being made to the database are taking so long that something within WordPress times out to the point that the database cant be connected too or the CPU is overloaded.
In these situations you would want to know which calls are being made which are too slow and where are they coming from, from within the site such that you can go and eliminate them. In my experience, this kind of thing may come from a plugin which is particularly inefficient with respect to how it has been coded.
This has happened to me twice in WordPress Projects I’ve worked on and it has always been from a newly installed or updated plugin so watch for this kind of thing just after installing or updating any plugins.
I also noted that the inefficient calls are often in areas which have large amounts of data like reviews on e-commerce sites being retrieved in a JOIN SQL Statement along with customers. If both of these tables have a lot of rows then these statements can be slow. You site will probably have a different specific cause but thats was my most recent example.
PROTIP: WordPress saves many things as POST Objects in the database and if there is a JOIN Statement that is from the posts table to the postmeta table then you can get slow results because the look up on meta fields is slow in WORDPRESS out of the box.
How to audit the slow MYSQL Calls made on your site:
Print out the SQL Queries on Page
I wrote another post on how to print out the SQL queries being made on a particular page in WordPress which you can read here but basically this post goes through some code with which you can add to print out the specific SQL statement and the speed that each one takes. You would want to find the longest ones (which are probably in the multiple of whole seconds long) as they will be the troublesome ones to eliminate.
The only problem with this approach is that where these particular SQL calls are coming from in the code isn’t necessarily obvious. This is why out next option is a better approach if your server has this feature.
Using PHP-XRAY
This is a piece of software that you server company may or may not have installed. On BlueHost I noticed that it was not installed but perhaps you could ask them too. On lightninghost.com I could see that it is there by default.
If you do have PHP-XRAY installed you can follow these instructions to set up a monitoring session. When the session is done you can review the results which will give you a list of MYSQL calls made to the database within that session (ranked by their slowness) and includes the PHP file and line with which that MYSQL call has been made.
Once you know which calls are slow and where to edit them you can go about either rewriting the code or perhaps making the call more efficient or different depending on your desire. In my experience its easier just to remove the offending PHP line but I assume that is not always possible. If its coming from a plugin then you might consider uninstalling that plugin if you don’t need it or another can be found (which does more or less the same thing) which is more efficient.
How to use PHP X_RAY:
Step 1: On the server cPanel find the PHP X-RAY icon:
Step 2: Click on the Icon and click ‘Stat Tracking’.
Step 3: Now add some settings like choosing your domain (you may have multiple on your server). Specify a mask by adding a particular URL or if you want all pages then just add an ‘*’.
If you want to run it more than 20 requests and simply track for a certain amount of time then hit ‘Advanced Settings’ and add the amount of time you’d like.
Step 4: Hit run and then wait for the period of time you set to expire. Then go back and click on the ‘Eye‘ Icon in the list of scans.
Step 5: PHP X-RAY will put the slowest queries at the top of the scan results and you can click into each slow MYSQL call and see how long it took and also which files are to blame for the slow call.
Step 6: From here you can identify the files which have problems and look to either stop them from running by editing the code in that file or perhaps optimise the MYSQL Calls themselves in order keep them but make them faster.
PROTIP: Often times the code can be edited by overriding the file itself. Instead of editing the file directly (which will be overwritten if you update the plugin and hence your alterations will be lost) you should create a child theme as a way to override theme files (if thats where the slow call is coming from) or override a template file if the culprit is instead in a plugin and not a theme.
Here are a few resources to help with child themes and overriding plugin files if you need to do this.
- WordPress Guide on Child Theme Development
- Hostinger’s Guide on creating a child theme.
- An example of overriding a plugin template
Learn WordPress customization throughly with an online course at Udemy
If you are having difficulty understanding this tutorial and then I suggest you start from scratch with WordPress in general. The best way to learn is with a better all around general understanding of WordPress. Check out some of the courses on Udemy related to WordPress. They cover developer level courses and more non-dev CMS editor type tutorials as well.
