Snippet – Spot bad queries in WordPress the quick&dirty way

Here’s a little piece of code I use for debugging when I evaluate sites and themes which I don’t know in detail.
I simply drop this in a file and include this file from within the themes’ functions.php or simply by dropping it in wp-content/mu-plugins as something like 0-my-debugging.php or the like.
Then all you need to do is add your remote IP address to the $my_debug_ips array and you’ll receive a log in /tmp/debug.log the next time you reload the page.
This piece of code gives you a log of the queries that are run along with the backtrace from where they were called along with a timestamp.
This makes it relatively easy to identify slow running queries and/or code segments that could benefit from caching.
When define( 'EXPLAIN_QUERIES', true ) is set in the script it will also run a MySQL EXPLAIN on each of the queries that are executed and dump the result to the logfile as well.
It also hooks into http_request_args to log any calls that are made using the HTTP API.
As a bonus you can also use my_var_log( $msg, $trace=true ) somewhere in your code to dump the content of other variables.

$my_start = microtime( true );
$my_debug_ips = array(
	'111.222.333.444', // your remote IP address
);

if ( !in_array( $_SERVER['REMOTE_ADDR'], $my_debug_ips ) )
	return;

define( 'EXPLAIN_QUERIES', true );

add_filter( 'query', 'my_query_log' );
function my_query_log( $q ) {
	my_var_log( $q );
	if ( defined( 'EXPLAIN_QUERIES' ) && EXPLAIN_QUERIES ) {
		global $wpdb;
		remove_filter( 'query', 'my_query_log' );
		$res = $wpdb->get_results( $wpdb->prepare( 'EXPLAIN ' . $q ) );
		my_var_log( $res, false );
		add_filter( 'query', 'my_query_log' );
	}
	return $q;
}

function my_var_log( $msg, $trace=true ) {
	global $my_start;
	$time = microtime( true ) - $my_start;
	error_log( sprintf( "%s - %.2fs - %s - %s\n", date( 'Y-m-d H:i:s' ), $time, var_export( $msg, true ), $_SERVER['REQUEST_URI'] ), 3, '/tmp/debug.log' );
	if ( true === $trace ) {
		$trax = array();
		$trace = debug_backtrace();
		foreach ( $trace as $key => $trc ) {
			$trax[$key] = $trc['file'] . '::' . $trc['line'] . ' - ' . $trc['function'];
		}
		error_log( var_export( $trax, true ) . "\n", 3, '/tmp/debug.log' );
	}
}

add_filter( 'http_request_args', 'my_httpapidebug', 10, 2 );
function my_httpapidebug( $r, $url ) {
	$r['_url'] = $url;
	if ( function_exists( 'my_var_log' ) )
		my_var_log( $r );
	return $r;
}

No comments yet... Be the first to leave a reply!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: