26

Is there a way to get the execution time of the last executed query in mysql?

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
mck89
  • 18,918
  • 16
  • 89
  • 106

3 Answers3

40

mysql has a builtin profiler. You can enable profiling by issuing set profiling=1; and use show profiles; to get execution times.

Alex Yeremenko
  • 115
  • 13
soulmerge
  • 73,842
  • 19
  • 118
  • 155
  • @bobobobo Why do you say "deprecated"??? See... http://dev.mysql.com/doc/refman/5.6/en/profiling-table.html Also see my answer – G-J Apr 13 '13 at 11:55
  • 1
    Well the docs say [SHOW PROFILES Syntax](http://dev.mysql.com/doc/refman/5.6/en/show-profiles.html) "is deprecated and will be removed in a future release", – bobobobo Apr 13 '13 at 17:48
9

if using PHP .. you can use microtime() before the query and after the query to figure out how long it took for the query to execute.

$sql_query='SELECT * FROM table';
$msc=microtime(true);
$mysql_query($sql_query);
$msc=microtime(true)-$msc;
echo $msc.' seconds'; // in seconds
echo ($msc*1000).' milliseconds'; // in millseconds
DonOfDen
  • 3,968
  • 11
  • 62
  • 112
Sabeen Malik
  • 10,816
  • 4
  • 33
  • 50
  • 2
    This doesn't tell you how long it took MySQL to execute the query. It tells you how long it took for PHP to send the request, wait for it to be received, and to receive the result. It will be slowed down by whatever may be running on the PHP client and if there is any latency between the PHP client and the MySQL server, the results will be further inaccurate. – Aaron J Spetner Jan 05 '17 at 09:28
  • I get strange results from this, sometimes it reports `2.15 s` for 8000 rows, other times reports `1,503,023,491.52 s` for 6000 rows. If I apply `number_format($msc,2)` then I get event stranger results like negative numbers. – Slam Aug 18 '17 at 02:38
5

Try this...

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

/* It goes without saying that this is your actual query that you want to measure the execution time of */
$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