tags:

views:

400

answers:

5

Hello, how would I count the number of sql queries executed on one page load?

I have a similar script to time taken for page to be generated, but not for how many queries have been executed.

You know what I mean, such as on SMF forums, in the footer, they have:

Page created in 0.136 seconds with 7 queries.

in the footer?

Replacing all of the mysql_query(ies) isn't really an option, there are way too many mysql_queries to replace, although I could spent a day doing it if needs be.

Thanks

A: 

To count the number of queries, you need to count the number of queries. Sorry to sound redundant, but it really is that simple.

Make a counting function (mysql_query_counted?), then use grep to search through your codebase for mysql_query(, and it shouldn't take more than an hour or two. Possibly even think about using sed or similar to replace the function calls.

A note on SMF and similar that have this built-in. They use DB abstraction layers, so they are already using their own query function, and adding query counting at a later date would have been as simple as adding a line incrementing a counter to to that function. +1 for abstraction and encapsulation I suppose.

Matthew Scharley
+4  A: 

SMF does its query counting by having its own custom query function:

function db_query($db_string, $file, $line)
{
    global $db_cache, $db_count, $db_connection, $db_show_debug, $modSettings;

    // One more query....
    $db_count = !isset($db_count) ? 1 : $db_count + 1;

    ...

The simplest way to achieve what you're trying to do would be to do the same; make a wrapper for mysql_query and use that instead of mysql_query.

Sebastian P.
+4  A: 
SHOW SESSION STATUS LIKE 'Questions'
Quassnoi
+1  A: 

You can get the number of queries ever executed by calling.

show session status like "Queries";

Call this at the beginning and at the end of page creation, and then you can see how many queries there have been. Don't forget that this command itself is also counted as one.

Zed
Should this be "Questions" or does it matter? I ran "queries" and did not get a result, but "Questions" seemed to work fine.
jmccartie
here's the explanation on them: http://dev.mysql.com/doc/refman/5.0/en/server-status-variables.html
Zed
A: 

Here's an example which might be easier to follow than the SMF one.

class QueryLogger
{
    public $queries = array();

    public function query($sql)
    {
     $start = microtime(true);

     $query = mysql_query($sql);

     $queries[] = microtime(true) - $start;

     return $query;
    }

    public function getCount()
    {
     return sizeof($this->queries);
    }

    public function getTime()
    {
     return array_sum($this->queries);
    }
}

$queryLogger = new QueryLogger;
$query1 = $queryLogger->query('...');
$query2 = $queryLogger->query('...');
echo 'Ran '.$queryLogger->getCount().' queries in '.$queryLogger->getTime().' seconds.';
whichdan
Regarding the time it would take to update your code, you could do a search/replace. Search for 'mysql_query' and replace with '$queryLogger->query'
whichdan
Though you'll have to make sure that $queryLogger is instantiated.
Sebastian P.
hmm, and for mysqli, there's not much of a change is there?
Shamil
Call to a member function query() on a non-object in global/config.php on line 24 - this is where $queryLogger->query is first used.
Shamil
note, that all of the code is in procedural and not OOP.
Shamil
As Sebastian said, did you make sure to run $queryLogger = new QueryLogger; before you called query()?
whichdan