31

I execute MySQL queries from PHP and would like to know how time consuming they are. Is there any way to get the execution time of a MySQL query from PHP?

I also wonder if the execution time depends on how loaded is the web server. I can imagine that a query will take more time to execute if the server is busy with other queries. On the other hand, I can imagine that, if the server is busy, the query will just wait for its turn and then it will be executed (without any queries executed in parallel) and than the waiting time is not included into the execution time. So, what scenarios (out of two) is correct?

oers
  • 18,436
  • 13
  • 66
  • 75
Roman
  • 124,451
  • 167
  • 349
  • 456

3 Answers3

64

There is probably some way to do this via MySQL, however, the easy (and reliable) way is using PHP's microtime function, which returns the current time as milliseconds.

microtime() returns the current Unix timestamp with microseconds. This function is only > available on operating systems that support the gettimeofday() system call.

getasfloat - When called without the optional argument, this function returns the string "msec sec" where sec is the current time measured in the number of seconds since the Unix Epoch (0:00:00 January 1, 1970 GMT), and msec is the microseconds part. Both portions of the string are returned in units of seconds.

If the optional get_as_float is set to TRUE then a float (in seconds) is returned.

Some example code:

$sql = '...';
$msc = microtime(true);
mysql_query($sql);
$msc = microtime(true)-$msc;
echo $msc . ' s'; // in seconds
echo ($msc * 1000) . ' ms'; // in millseconds
Christian
  • 27,509
  • 17
  • 111
  • 155
  • thank you for the answer. By the way, may be you also know about the second part of the question? Should this time depend on how strongly is server loaded? – Roman Dec 21 '10 at 17:38
  • 1
    Roman - Yes it does, of course. It works over TIME not CPU STEPS, as such everything may affect it, even a new connection (as an example). – Christian Dec 21 '10 at 18:15
  • 6
    You might also want to mark an answer as right. :) – Christian Dec 22 '10 at 14:15
  • You are correct that there is a way to do it in mysql and that is the recommended way as `microtime()` takes time to run itself. If you want the info direct from mysql... See my answer. – G-J Apr 12 '13 at 21:03
  • millisecond == second*1000 ?? Looks like you have it versa-vice... – ZurabWeb Jan 31 '14 at 16:33
  • 2
    @Piero microtime returns the time in seconds. To print it out in msec, you need to multiply by 1000. Ex; `0.0021s * 1000 = 2.1ms` – Christian Feb 01 '14 at 09:44
  • This is only reliable to some degree. If you try to measure execution time of _very_ fast query (like primary key lookup on evenly spread integers) or your php scrip is on different server than database you might not get accurate enough times. One solution to this issue is parsing SHOW PROFILE output. – matt Oct 24 '14 at 10:07
  • @lucek I agree, but most of the time people want to know the query speed for PHP's perspective, even taking connection speed into account (for instance). – Christian Oct 25 '14 at 10:35
10

microtime() takes time to execute itself. If you want to get the data straight from mysql, do this...

mysql_query("SET profiling = 1;");
if (mysql_errno()) { die( "ERROR ".mysql_errno($link) . ": " . mysql_error($link) ); }

$query="SELECT some_field_name FROM some_table_name";
$result = mysql_query($query);
if (mysql_errno()) { die( "ERROR ".mysql_errno($link) . ": " . mysql_error($link) ); }

$exec_time_result=mysql_query("SELECT query_id, SUM(duration) FROM information_schema.profiling GROUP BY query_id ORDER BY query_id DESC LIMIT 1;");
if (mysql_errno()) { die( "ERROR ".mysql_errno($link) . ": " . mysql_error($link) ); }
$exec_time_row = mysql_fetch_array($exec_time_result);

echo "<p>Query executed in ".$exec_time_row[1].' seconds';
G-J
  • 1,080
  • 2
  • 16
  • 32
-3

To get it right out of MySQL you can use the general log.

If its not already on, turn it on:

SET GLOBAL general_log = 'ON';

To see the data after you've run your query:

SELECT * FROM mysql.general_log;

Among other things, you will get the query execution time, just like you've probably seen in phpmyadmin.

profitphp
  • 8,104
  • 2
  • 28
  • 21