How to profile SQL queries in MVC framework?

Question

Hello All,

I have been working on my own php MVC framework. I just wanted to get your ideas on how to profile all the queries run on my framework. I have done other profiling stuff but i have no clear idea of how to profile the sql queries too for the users to see if they turn profiling on.

Thanks

Answer 1

It really depends on how much information you want, but to be a bit generic for you I would consider something this this (the class is whatever you are using to interact with the database):

<?php
//Your Database access class
class DB{
    //Create private property for holding profiling data
    private $sqlDataProfile = array();

    function query($sql){
        $starttime = time();//Get the time before the query

        //Query and whatnot here

        //Start profiling here:
        $this->addSqlProfile($sql,$starttime);
    }

    //Get total number of queries run
    function getNumberOfQueries(){
        return count($this->sqlDataProfile);
    }

    private function addSqlProfile($sql,$starttime){
        //Create temporary array
        $tempArr = array(
          'sql'         => $sql,
          'time'        => time()-$starttime
        );

        //Push tempArr to the sqlDataProfile var
        $this->sqlDataProfile[] = $tempArr;
    }
}

$db = new DB;
$db->query('SELECT * FROM table');
echo $db->getNumberOfQueries();
?>

This is obviously a very simple example, but this way would be both very easy, as well has pretty powerful in terms of things you can store. For example you could also store the number of rows affected by a query, the number of tables a query touches, etc.

Answer 2

Zend Framework is a good example for you. You can see SQL queries under the web site. If you write your own framework you can follow that way. Users can open or close one boolean variable. If They set it "true" you can write sql queries under the web page. With that solution coders can see which queries are running by MySQL.