The Extended Query Profiler

MySQL’s built in Query Profiler is a great tool for finding out extended information about your queries. In order to integrate your Database Access Layer with the Query Profiler you will need to setup the query_profiler_callback configuration option. This can be done by either passing it into the constructor or calling the setQueryProfilerCallback method later after the profiler is instantiated.

The function that you setup for the callback will be passed the raw SQL query. You will need to ensure the query profiler is being enabled within your DAL and will need to setup the callback function to locate the query profile data for the correct query that’s passed to it. Below is an example of what a method like this may look like.

    public static function profileQuery($sql) {
      if (!isset(self::$profiles)) {
        $res = mysql_query('show profiles');
        while ($row = mysql_fetch_assoc())
          self::$profiles[] = $row;
      }

      foreach (self::$profiles as $i => $profile) {
        if ($profile['Query'] == $sql) {
          $data = array();
          $res = mysql_query('show profile for query ' . $profile['Query_ID']);
          while ($row = mysql_fetch_assoc())
            $data[] = $row;

          unset(self::$profiles[$i]);
          return $data;
        }
      }
      return array();
    }

The most important thing that the callback must do is return an array of arrays containing the following structure:

Array(

  0 => Array(

    ‘Status’ => ‘(initialization)’,

    ‘Duration’ => 0.000001),

  1 => Array(

    ‘Status’ => ‘checking query cache for query’,

    ‘Duration’ => 0.000006))

This would allow accessing the data in there as follows:

echo $data[0]['Status']; // Would return '(initialization)'

• Currently none of the additional profile type data is used. Returning it will not cause errors, but it will not be displayed if it is returned.
• As long as the callback used returns it’s data in the aforementioned structure the data will be displayed appropriately. You are not limited to returning the data exactly as MySQL does, nor are you limited to only MySQL. You could easily setup this callback to collect the appropriate data you wish to see for any database, regardless of the engine used, so long as it returns the data in the correct format.