How can I count the total number of MySQL queries used per page?

Question

Is there a built-in function in PHP or MySQL that will provide the total number of MySQL queries used on a page? I've seen on a lot of sites (mainly forums) they have a message at the bottom saying something like "Page generated in 0.6 seconds with 20 queries".

If there's nothing built in then I'll add something to my database class to count them, but it seems like the kind of functionality that would already be available.

Answer 1

There is nothing built into PHP's native mysql/mysqli extensions. If you're using a DB abstraction layer, there may be some type of analysis/benchmarking built in that will give you that info.

Off-hand, I know Kohana does this with the Profiler library. I would assume that most frameworks' DB libraries do something similar.

If you are hesitant to use a 3rd party framework or database abstraction layer, I have had success by simply subclassing PHP5's built-in PDO class. You can add some basic benchmarking information and query counts, and pass the query() or execute() calls through to the parent class.

Answer 2

What I do is make an sql query class that counts, it adds 1 to a variable called querycount every time the query methods in the class are run, That way I have a running total.

Answer 3

Option one would be to pass all of your queries through a wrapper:

function custom_mysql_query($sql)
{
    $GLOBAL['query_count'] ++;
    return mysql_query($sql);
}

Please note that's for illustration only and without error handling, etc.

You could query MySQL for the number of queries run:

mysql> SHOW STATUS LIKE 'Com_select';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_select    | 2     | 
+---------------+-------+
1 row in set (0.00 sec)

You might want to do something like:

SHOW STATUS LIKE 'Com_%';

and then add together Com_select, Com_update, Com_insert and Com_delete

Answer 4

To calculate the total time spent generating the page you can use the following:

<?php
$start = microtime(true);
...
/* your code here */
...
$finish = microtime(true);

$time_spent = $finish - $start;
?>