Plugin development tip: Analyse your database queries

If you are an advanced plugin developer, you certainly know, you should keep number of db queries made by your plugin as small as possible. Getting the total number of database queries made when generating page is obvious: get_num_queries() returns the number.

What is not so obvious, is how to display actual performed MySQL queries (strings) – which might be useful in various scenarios of performance optimization. WordPress is prepared for that task:

1. step: Add into your wp-config.php file this constant/value which enables remembering of all performed queries.

define ('SAVEQUERIES', true);

2. step: Add into your template file (hopefully footer.php) following code (which makes the output).

<?php
if (SAVEQUERIES  AND  current_user_can('level_10')) {
        echo '<pre style="text-align:left">'; print_r($wpdb->queries); echo '</pre>';
} ?>

What the result will look like:

Array
(
    [0] => Array
        (
            [0] => SELECT option_name, option_value FROM wp283_options WHERE autoload = 'yes'
            [1] => 0.00113081932068
            [2] => require, require_once, require_once, require_once, is_blog_installed, wp_load_alloptions
        )

    [1] => Array
        (
            [0] => SELECT option_value FROM wp283_options WHERE option_name = 'ras_always_visible' LIMIT 1
            [1] => 0.000532150268555
            [2] => require, require_once, require_once, require_once, include_once, get_option
        )

    [2] => Array
        (
            [0] => SELECT * FROM wp283_users WHERE user_login = 'admin'
            [1] => 0.000302076339722
            [2] => require, require_once, require_once, require_once, WP->init, wp_get_current_user, get_currentuserinfo, wp_validate_auth_cookie, get_userdatabylogin, get_user_by
        )

    [3] => Array
        (
            [0] => SELECT meta_key, meta_value FROM wp283_usermeta WHERE user_id = 1
            [1] => 0.000527858734131
            [2] => require, require_once, require_once, require_once, WP->init, wp_get_current_user, get_currentuserinfo, wp_validate_auth_cookie, get_userdatabylogin, get_user_by, _fill_user
        )

    [4] => Array
        (
            [0] => SELECT option_value FROM wp283_options WHERE option_name = 'sidebars_widgets' LIMIT 1
            [1] => 0.000389814376831
            [2] => require, require_once, require_once, require_once, do_action, call_user_func_array, wp_widgets_init, register_widget, WP_Widget_Factory->register, WP_Widget_Recent_Comments->WP_Widget_Recent_Comments, is_active_widget, wp_get_sidebars_widgets, get_option
        )

    [5] => Array
        (
            [0] =>  SELECT SQL_CALC_FOUND_ROWS  wp283_posts.* FROM wp283_posts  WHERE 1=1  AND wp283_posts.post_type = 'post' AND (wp283_posts.post_status = 'publish' OR wp283_posts.post_status = 'private')  ORDER BY wp283_posts.post_date DESC LIMIT 0, 10
            [1] => 0.000468015670776
            [2] => require, wp, WP->main, WP->query_posts, WP_Query->query, WP_Query->get_posts
        )

        [...]

Explanation:

• [0] – the query string
• [1] – time taken
• [2] – backtracing (functions that anticipated the db query)