Last Updated:

SQL query execution time

So, we need to identify the time spent executing SQL queries ? It's not very easy, but it's not difficult either. Let's start by defining the task. It is necessary to give the full time spent on generating the page and the time spent on executing SQL queries, it would still be great to display a percentage of the total time.

First, let's write a function that gives the time it took to execute its code:

function do_something(){
        $mtime = microtime(); 
        $mtime = explode(" ",$mtime); 
        $mtime = $mtime[1] + $mtime[0]; 
        $tstart = $mtime; 
    //here is the code to execute 
    //.........

        $mtime = microtime(); 
        $mtime = explode(" ",$mtime); 
        $mtime = $mtime[1] + $mtime[0]; 
        $tend = $mtime; 
        $tpassed = ($tend - $tstart); 
        return($tpassed);
    } 

For our specific task, we need to modify this function so that SQL queries are executed:

    //request is passed as an argument
     function do_query($query){
     //connect two global variables
         global $result;
         global $qnum;
     //request counter
         $qnum++;
     // detect the start time
         $mtime = microtime();
         $mtime = explode(" ",$mtime);
         $mtime = $mtime[1] + $mtime[0];
         $tstart = $mtime;
     //execute the request
             $result = MYSQL_QUERY($query);
     // detect the end time
         $mtime = microtime();
         $mtime = explode(" ",$mtime);
         $mtime = $mtime[1] + $mtime[0];
         $tend = $mtime;
         $tpassed = ($tend - $tstart);
     //return the time spent on the request
         return($tpassed);
     }

Now we have a function that counts requests and gives the execution time :) Here's how it should be used:

//Don't forget to declare these two variables somewhere at the beginning of the script:
     $result=0;
     $qnum=0;
//...
//Function call:
     $sql_time+=do_query("SELECT * FROM SOME_TABLE");
//Now we can parse the received data:
     while($row = mysql_fetch_array($result)){
         print($row['Text']);
     }

In the final script, you still need to plot the full execution time, in the same way that was used in the function. At the bottom is the code of such a script, which will work if you insert real SQL queries and connect to the database.

<?
//We detect the start time
    $mtime = microtime();
    $mtime = explode(" ",$mtime);
    $mtime = $mtime[1] + $mtime[0];
    $tstart = $mtime;

//Connect to the database:
    include 'connect.php';

//Declare variables
    $result=0;
    $qnum=0;

//Declare our function
    function do_query($query){
    global $result;
    global $qnum;
        $qnum++;

        $mtime = microtime();
        $mtime = explode(" ",$mtime);
        $mtime = $mtime[1] + $mtime[0];
        $tstart = $mtime;

        $result = MYSQL_QUERY($query);

        $mtime = microtime();
        $mtime = explode(" ",$mtime);
        $mtime = $mtime[1] + $mtime[0];
        $tend = $mtime;
        $tpassed = ($tend - $tstart);
        return($tpassed);
    }

//Next script body
    $sql_time+=do_query("SELECT * FROM SOME_TABLE");
//Process data
    while($row = mysql_fetch_array($result)){
        print($row['Text']);
    }

//Example of another request
    $sql_time+=do_query("SELECT * FROM ANOTHER");
//Process data
    $row = mysql_fetch_array($result);
    print($row['Another_Text']);

//Notice the end time
    $mtime = microtime();
    $mtime = explode(" ",$mtime);
    $mtime = $mtime[1] + $mtime[0];
    $tend = $mtime;
    $total = ($tend - $tstart);

//Give time:
    printf("Query SQL: $qnum, mysql time: %f,
      total elapsed: %f seconds !", $sql_time, $total);

// Calculate the percentage of time:
    $sqlpercent = ($sql_time*100)/$total;
    print('MySQL Time Percentage: '. round($sqlpercent, 2) . '%');
?>

That's it! :)