如何在MVC框架中分析SQL查询？

Question

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.