In this post I’ll give you the code to show the various SQL queries used to load a page in WordPress easily as well as some code to show this information based on loading a URL instead of having to edit your files every time.
I’ll also explain in what scenarios you might want to use this method and therefore why its useful. You should be comfortable editing files on your site in order to follow this tutorial.
Table of Contents
- Why would I want to see all of the SQL queries on a particular page?
- How can I see the SQL queries used on any WordPress Page?
- Best Practice Implementation to see SQL Queries
- Learn to customize WooCommerce Fully with an online Udemy Course
Why would I want to see all the SQL queries on a particular page?
To copy some SQL to re-use someplace else:
When using a WordPress site you may have installed some plugin which gets certain information from the database via SQL and displays it on a page and you want to take that same information into a shortcake or another function in WP and also take the same information and display or use it in a different way or in a place with which the original plugin doesn’t allow you to do.
If you can see the SQL query with which they used then you can easily copy it, once you can see it (see below), and use it yourself in your own custom function of shortcake for whatever use you require.
To learn what kinds of data is stored where in WordPress:
Similar to the above, you might have a plugin on your site which displays data that you are interested in also using but you don’t know where it is in the WordPress Database.
You see, sometimes data is stored in multiple tables and an SQL Query with a JOIN statement in mysterious tables or you are not even sure of the table name in which data is stored in the case it is a single table.
If you can see the SQL query responsible for gathering this data from the database, across one or multiple tables, then you can easily learn where to look for this and what particular columns are useful or not.
The alternative is sometimes (or often in the case of plugins which often don’t document this kind of detail) that you have to put through the live database in PHPMYADMIN looking for the data which can be a huge waste of time.
To try to speed up the loading of a page if it loading slowly due to some inefficient or lengthy SQL query:
Sometimes, especially if you are dealing with a page which deals with processing a large amount of data, a particular query may be using up too much of the servers resources which basically means the page is very slow to load.
In this case you will want to see what queries are executed on the page in order to try and clean it up or make it faster.
The other side of the coin is that you may want to see how a query on a page is structured because it is actually faster than what you have been able to do and you can similarly print it out and copy it to get a fast page load.
How can I see SQL queries on any WordPress page?
- You will need to edit (adding a line) one of the WordPress core files named wp-config.php.
- Between the custom comments area of the file you need to add the below line of code which will make WordPress start to record and save the queries being made as they are executed.
/* Add any custom values between this line and the "stop editing" line. */
define( 'SAVEQUERIES', true );
/* That's all, stop editing! Happy publishing. */- Now that WordPress is recording the queries we can begin to print them out under whatever circumstances we want.
- To print out all of queries as well as the amount of queries executed we can use the following piece of code.
global $wpdb; // you need to set this explicitly to use the below functions
print_r($wpdb->num_queries); // This will print out the amount of queries executed on a page
echo '<pre>';
print_r($wpdb->queries); // This will print out the actual queries themselves so you can view how they relate to the database and find table names or copy the SQL statements yourself.
echo '</pre>';Best practice implementation to see SQL Queries on page load:
There are several ways to use these statements and Ill try to give a few tips and tricks on how to use these usefully on your live or development site.
Simple Implementation:
The easiest way to use this is to edit two files and in effect this will print out all queries on all pages all the time.
- First, you edit the wp-config.php file by adding the below code between the ‘start and stop editing comments.
- PROTIP: You have to add this between a set of comments which WP provides to you otherwise there can be issue.
- PROTIP: If you don’t wrap this in the check then there is a performance hit to your page speed when you aren’t using it.
/* Add any custom values between this line and the "stop editing" line. */
// If you don't wrap this in the check then there is a performance hit to your page speed when you aren't using it
$showSQL = sanitize_text_field($_GET['showsql']);
if( $showSQL == 'true' ){
define( 'SAVEQUERIES', true );
}
/* That's all, stop editing! Happy publishing. */- Second, you edit the functions.php file by adding the below code at the end of the file:
global $wpdb;
print_r($wpdb->num_queries);
echo '<pre>';
print_r($wpdb->queries);
echo '</pre>';- Doing this will print out all queries executed on a page, the amount of them as an INT on all pages all the time.
- PROTIP 1: If you are doing this on your live site then you will need to turn this off again because public users will also be able to see this.
- PROTIP 2: If you are worried about users seeing this do it late at night or early morning when your users are usually not on the site. This will effect people the least amount possible and provide a better user experience for them.
A more sophisticated implementation:
A more sophisticated approach is to take the code from the simple implementation above and wrap it in some other code which will allow us to
- One, only print the SQL statement information in the case we are an ‘admin user’ in WordPress and,
- Two, only print out our SQL statement info if you provide a specific URL as a GET variable. The benefit of this is that you on’t have to edit the wp-config.php and functions.php files every time you want to see the queries.
- Below we will run through how to execute this.
Step 1:
Just as in the simple implementation you should add the below code to the wp-config.php file (at the end is where I usually put things like this).
define( 'SAVEQUERIES', true );Step 2:
Next we will wrap the specific code that prints out the SQL queries in a ‘check’ which sees if a specific URL has been used, the user is logged in and the user is an administrator.
PROTIP: We add so many checks for security purposes – you do not want anyone to be able to see the various structures of your SQL database which they would more easily be able to do if they happened to guess the GET URL variable value and were not logged in or not an admin.
Add the below code to your functions.php file,
function printrDatabase() {
global $wpdb;
var_dump($wpdb->num_queries , $wpdb->queries);
}
add_action('init', function() {
$showSQL = sanitize_text_field($_GET['showsql']);
if(
is_user_logged_in() && // checks if the user is logged in
current_user_can('administrator') && // checks if the current user is an administrator
$showSQL == 'true' // checks to se if showsql GET variable has been set to true in the URL
){
printrDatabase();
}
});Now you have added the lines of code to the relevant files on your server you can now load a specific URL, when logged din as administrator in order to see the various SQL information much quicker than having to do this every time.
PROTIP: Because we are wrapping it in a GET variable check we are able to leave this code up on the server permanently and trigger the SQL information to be printed to screen when we specifically load a URL (while be logged in as admin for security obviously).
Step 3: What URL should I load?
Load something like, https://yoursite.com?showsql=true
Again, if you are logged in as admin and add the ‘?showsql=true‘ on the end of your page URL then the SQL info will be printed out.
Step 4: View the code:
You will see now at the top of the page that there will be an unformatted print out of an INT value representing the amount of queries run on the page and then what is probably a large array which contains information about each one of the SQL queries called on the page.
For example you will see something like this: You can see that there are 33 calls on the page in my example and the first part of the array shows you the specific string which is used on the DB so you can copy or look at it.
int(33)
array(33) {
[0]=> array(5) { [0]=> string(73) "SELECT col3, col2 FROM xyz_options WHERE autoload = 'yes'"
......
}Learn to customize WooCommerce Fully with an online Udemy course
If you’d like to know more about how to customize WooCommerce completely check out these courses from Udemy which are low cost, online and have been completed by thousands of people.
