mysql query times

[itsakoo]

Hi,

Anybody know whether the time tacken for mysql to conduct a query is availible to PHP? Had a look through the mysql function but couldn't see anything.

Many thanks,

iTSAKOO::

[dotwebbie]

I'm not sure I get what you are saying . . . 😕

[mrmufin]

I made a generic timer class that can be used to time anything; queries, connection times, page generation times, etc.

It goes like this:

<?php

class Timer {

    var $start;
    var $finished;
    var $elapsed;

    function Timer() {
            if($this->start) {
                    return;
            }
            list($u,$s) = explode(' ', microtime());
            $this->start = (float)$u + (float)$s;
            return $this->start;
    }

    function Stop() {
            list($u,$s) = explode(' ', microtime());
            $this->finished = (float)$u + (float)$s;
            $this->elapsed = $this->finished - $this->start;
            return $this->finished;
    }

    function Elapsed() {
            if($this->start) {
                    if(!$this->finished) {
                            $this->Stop();
                    }
                    $this->elapsed = $this->finished - $this->start;
                    return $this->elapsed;
            }
            return;
    }
}

?>

You would implement it like this:

$q = "SELECT * FROM sometable";

$t = new Timer();
$r = mysql_query($q);
if(!$r || !mysql_num_rows($r)) {
	// query returned no results or failed, so we stop the timer:
	$t->Stop();
} else {

// Stop the timer here if you're just interested in how fast MySQL got a result set.
// echo($t->Elapsed());
	while($data = mysql_fetch_array($r)) {
		// do stuff with the results
	}
}
// Stop the timer and get the elapsed time:
echo $t->Elapsed();

Works for me... have fun...

[gid]

I personally have a wrapper function to mysql_query that does the job, since usually you only care about the initial query. The fetches should be rather trivial with small data, and there really not too much you can do to optimize fetches anyway, besided only querying for only the frields you want, instead of "select *" which you should be doing in production anyway. 🙂

function mysql_time_query($input) {
$StartTime = split(" ",microtime(),2);

    echo "<p>$input<p>";
    $toreturn = mysql_query($input);

    $StopTime = split(" ",microtime(),2);
    $RunTime =  ($StopTime[0] - $StartTime[0]) + ($StopTime[1] - $StartTime[1]);
    echo "<p>\nApprox query time: ". number_format($RunTime,6) ." seconds.<BR>\n";

    return $toreturn;

}

[dotwebbie]

Originally posted by dotwebbie
I'm not sure I get what you are saying . . . 😕

Where's my dunce hat. 🙁

phpbuilder.com put up an article a while ago about timing your scripts as well.

[itsakoo]

Hi,

Sorry, been of net for a few days hence the lack of a response. Many thanks for the replies, both will do the job nicely. I was thinking that as this information is returned when using mysql at the command line it might be availible through the php mysql api, but I guess there's not really much call for it. Anyhow, the methods given above will give the time for both the mysql query and the time taken to process the php, i.e. the time taken to actually return the resulting page which is probably a lot more usefull. 🙂

iTSAKOO::