This is a short guide on how to measure the execution time of an SQL query with PHP. This can be extremely useful for debugging purposes (frameworks such as CakePHP have this built into their development mode).
An example of query execution time being measured in PHP:
//Record the start time before the query is executed.
$started = microtime(true);
//Execute your SQL query.
mysql_query("SELECT id, name FROM users LIMIT 5");
//Record the end time after the query has finished running.
$end = microtime(true);
//Calculate the difference in microseconds.
$difference = $end - $started;
//Format the time so that it only shows 10 decimal places.
$queryTime = number_format($difference, 10);
//Print out the seconds it took for the query to execute.
echo "SQL query took $queryTime seconds.";
A step-by-step guide to the code snippet above:
- We record the UNIX time in microseconds by using PHP’s microtime function. We record this timestamp BEFORE the SQL query is executed.
- We execute our SQL query.
- We record the UNIX time again.
- We work out how many microseconds have passed by subtracting the $start time from the $end time.
- We format the result so that only 10 decimal places are shown (you can change this to whatever you want to).
- We print out the formatted result.
You can also use this logic to measure the execution time of other PHP operations, such as function calls and API calls.