PHP + MySQL profiler

Question

You know how vBulletin has a sql profiler when in debug mode? How would I go about building one for my own web application? It's built in procedural PHP.

Thanks.

Answer 1

http://dev.mysql.com/tech-resources/articles/using-new-query-profiler.html

The above link links how you can get al the sql profile information after any query.

Best way to implement it is to create a database class and have it have a "profile" flag to turn on logging of queries and the appropriate information as shown int he link above.

Example:

Class dbthing{
  var $profile = false;

  function __construct($profile = false){
    if($profile){
      $this->query('set profiling=1');
      $this->profile = true;
    }
    ...
  }

  function query($sql, $profile_this == false){
     ...
     if($this->profile && !$profile_this)
        $this->query("select sum(duration) as qtime from information_schema.profiling where query_id=1", true);
     ... // store the timing here
  }

}

Answer 2

I use a database connection wrapper that I can place a profiling wrapper arround. This way I can discard the wrapper, or change it, without changing my base connector class.

class dbcon {
    function query( $q ) {}
}
class profiled_dbcon()
{
    private $dbcon;
    private $thresh;
    function __construct( dbcon $d, $thresh=false )
    {
        $this->dbcon = $d;
        $this->thresh = $thresh;
    }
    function queury( $q )
    { 
        $begin = microtime( true );
        $result = this->dbcon->query();
        $end = microtime( true );
        if( $this->thresh && ($end - $begin) >= $this->thresh ) error_log( ... );
        return $result;  
    }
}

For profiling with a 10 second threshold:

$dbc = new profiled_dbcon( new dbcon(), 10 );

I have it use error_log() what the times were. I would not log query performance back to the database server, that affects the database server performance. You'd rather have your web-heads absorb that impact.