Capturing and examining queries

It’s straightforward to get WordPress to capture the MySQL queries it uses to generate each page view. We can then analyze the captured queries.

Capturing

WordPress offers a way to capture a list of the database queries it uses in each page view. It’s simple: define the global SAVEQUERIES symbol. You can define it permanently in your wp-config.php file with this line: define( 'SAVEQUERIES', true ); Or, you can define it at the beginning of a pageview and then examine the results with code like this:

add_action( 'init', 'capture_all_queries', 1);
add_action( 'shutdown', 'examine_all_queries', 99);

/* At the beginning of each pageview */
function capture_all_queries () {
    if ( ! defined( 'SAVEQUERIES' ) ) define( 'SAVEQUERIES', true );
}

/* At the end of each pageview */
function examine_all_queries () {
    global $wpdb;
    foreach ( $wpdb->queries as $query ) {
        $sqlText = $query[0];
        $queryTimeInSeconds = $query[1];
        $stackTrace = $query[2];
        $queryStartTime = $query[3];
        /* do something useful with the query */
}

Be careful: capturing queries has overhead. Don't do it unless you need to.

Analyzing

Now, your $sqlText is the fully prepared query that $wpdb ran for WordPress core, a plugin, or a theme. It was run using $wpdb->get_results() or one of its other methods. A typical captured query might look like this:

SELECT COUNT(*)
  FROM wp_posts
 WHERE post_type = 'post'
   AND post_status NOT IN ('spam','trash','pending')
   AND post_author = 12345

This particular example happens to look up the number of active posts for the author with the id 12345. Notice how WordPress captures fully fleshed-out queries with literal constants.

We could simply add up the $queryTimeInSeconds for all queries used for a pageview, and count them. Then we would know how hard the particular pageview pushed MySQL.

Fingerprinting

But, when we’re trying to figure out which queries are slowest, we need to figure out how to take fingerprints of the queries. In our example, that means we must reduce the captured query to something like this fingerprint.

SELECT COUNT(*)
  FROM wp_posts
 WHERE post_type = ?string?
   AND post_status NOT IN (?string?, ?string?, ?string?)
   AND post_author = ?integer?

In this fingerprint, ?string? means any string constant (like 'post'). And, ?integer? means any integer constant (like 12345).That gives us a way to identify the query pattern, not just the individual query. So, we can track how many of this sort of query WordPress uses. We need a getQueryFingerprint($sqlText) function to generate the fingerprint from the raw query text. One way to implement that function is with regular expressions and php’s preg_replace() function. A series of lines of code like this one can generate a useful fingerprint. An example of code to generate fingerprints from WordPress queries is here.

$fingerprint = preg_replace( '/= +\d+/', '= ?integer?', $sqlText );

Hashing similar queries

We can then use the fingerprint as the key to an associative array gathering data on queries. Experience shows that this a good way to generate a hash.

 substr( hash( 'md5', is_admin() . $fingerprint ), 0, 16 )

This hash uses WordPress’s is_admin() function to distinguish queries from dashboard — back-end — pages and content pages. That’s helpful because dashboard pages won’t get as much use as pages showed to visitors.