Any MySql Profilers

[hemi519]

Hi All,

Can anyone tell me any opensource mysql profiler? I want to see the execution time of all the queries.

[djr33]

I'm not sure on any specifics, but you should be able to find relevant information by searching for "MySQL analysis/analyzer" and "MySQL statistics", and perhaps "MySQL performance" as well.

I'm sure there's a package that can do this, but you could also relatively easily do it yourself. Just track the time from the start of the script to the end of the script (or by query). Lots of pages have a "processing time" displayed at the bottom.

Another way to do this would be to create a class that has a __construct() function, which starts a timer, and a __destruct() function, which stops the timer and records it (in a database? somewhere else?), and it just has a single function in it-- execute a mysql query. Does that make sense?

Something like this:

class mysqlprofiler
{
    function __create() {
       $this->time = time()+microtime();
    }
    public function query($q) {
        $this->q = $q;
    }
    function __destruct() {
        if isset($this->q) {
           mysql_query($q);
           $this->time = (time()+microtime())-$this->time;
           //track time:
           mysql_query('INSERT INTO mysqlprofiler ....'. $this->time');
        }
    }
}
function mysql_query_profile($q) {
    $m = new mysqlprofiler();
    $m->query($q);
    unset($m); //perform __destruct();
}

//untested, but should work in general, probably after some adjustments for your setup, especially with tracking the times. 

Of course you could also do a simpler version with just one function that does all of that, but using the class will allow you to make it more complicated if needed -- including additional operations like executing a search in a loop -- or for doing several queries at once. The simpler version would be:

function mysql_query_profile($q) {
   $t = time()+microtime();
   mysql_query($q);
   return (time()+microtime())-$t; //return the length as the output of this query, or put it in the DB now if you want
}