Time of the SQL queries execution

So, we have to note the time of the SQL query execution. Actually it is not very simple, but it is not enough difficult. So we have to show the total time of the page generation and SQL query execution.

At first we write the function that shows the time of its code executing.


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); 

}


According to our task we have to modify that function for SQL queries executing.

//query is passed as an argument

function do_query($query) 

{ 

//include two global variables

global $result; 

global $qnum; 

//query counter

$qnum++; 

//note the start time

$mtime = microtime(); 

$mtime = explode(" ",$mtime); 

$mtime = $mtime[1] + $mtime[0]; 

$tstart = $mtime; 

//execute the query

$result = MYSQL_QUERY($query); 

// note the finish time 

$mtime = microtime(); 

$mtime = explode(" ",$mtime); 

$mtime = $mtime[1] + $mtime[0]; 

$tend = $mtime; 

$tpassed = ($tend - $tstart); 

//return the time spent for query

return($tpassed); 

} 


Now we have function that counts queries and shows the execution time. It should be used as follows:

//Don’t forget to declare these variables at the beginning of the script

$result=0; 

$qnum=0; 

//... 

//function call: 

$sql_time+=do_query("SELECT * FROM SOME_TABLE"); 

while($row = mysql_fetch_array($result)) 

{ 

print($row["Text"]); 

}


In the final script we have to note the total time of the SQL query execution. Such script is shown below. It’ll start working if you insert real SQL queries and connect to the database.

<? 

// note 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; 

//Decalare our functions

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); 

} 

//The body of the script

$sql_time+=do_query("SELECT * FROM SOME_TABLE"); 

//Processed data 

while($row = mysql_fetch_array($result)) 

{ 

print($row["Text"]); 

} 

//Example of the another query

$sql_time+=do_query("SELECT * FROM ANOTHER"); 

// Processed data

$row = mysql_fetch_array($result); 

print($row["Another_Text"]); 

// note the finish time

$mtime = microtime(); 

$mtime = explode(" ",$mtime); 

$mtime = $mtime[1] + $mtime[0]; 

$tend = $mtime; 

$total = ($tend - $tstart); 

//Print the time: 

printf("SQL queries: $qnum, time mysql: %f, total: %f seconds !", $sql_time, $total); 

//Count the time percent

$sqlpercent = ($sql_time*100)/$total; 

print("Time percent on the MySQL: ". round($sqlpercent, 2) . "%"); 

?>



That's it!