Is there a way to get the execution time of the last executed query in mysql?
Add a comment
|
3 Answers
mysql has a builtin profiler. You can enable profiling by issuing set profiling=1; and use show profiles; to get execution times.
2 Comments
G-J
@bobobobo Why do you say "deprecated"??? See... dev.mysql.com/doc/refman/5.6/en/profiling-table.html Also see my answer
bobobobo
Well the docs say SHOW PROFILES Syntax "is deprecated and will be removed in a future release",
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
2 Comments
Aaron J Spetner
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.
Slam
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.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';
1 Comment
Aaron J Spetner
This is tagged as a MySQL question. Not everyone using MySQL is using PHP.