To get MYSQL query execution time in Query

Question

I am developing a query browser, working with some large database and I need to know the time taken for executing the query.

I can see the time once the query is executed succesfully in PHPMYADMIN.

Profiling or Showing rows 0 - 29 (2,000 total, Query took 0.0145 sec)

Ex: Profiling

SELECT * FROM `larger_table`;


Status                        Time
starting                     0.000026
checking permissions         0.000006
Opening tables               0.000014
System lock                  0.000010
init                         0.000022
optimizing                   0.000004
statistics                   0.000007
preparing                    0.000005
executing                    0.000001
Sending data                 0.014138
end                          0.000004
query end                    0.000002
closing tables               0.000005
freeing items                0.000091
logging slow query           0.000003
cleaning up                  0.000002

In Query Browser I can see the time it takes in the bottom of the browser window.

So how can I get the execution time of the query when submit the query for execution.

i.e When I give the following query:

SELECT * FROM `larger_table`;

The query should return the time for execution it.

This I should catch in PHP and show to the Users.When the user gives some query to executed in browser.

Is there any way to find the execution time when the query is submitted.?

Kindly check the Images where I marked the estimated time.

I found some thing Check this

Solution 1

Please use the code below

$sql_query = 'SELECT * FROM larger_table';
$msc = microtime(true);
mysql_query($sql_query);
$msc = microtime(true) - $msc;
echo $msc . ' seconds'; // in seconds
echo ($msc * 1000) . ' milliseconds'; // in millseconds

Solution 2

Check out below -

$sql_query='SELECT * FROM tablename';
$result = mysql_query($query);
$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';  

OutPut - Query executed in 0.000121 seconds

Comments

[DonOfDen]

Hi Kapil, Thanks for your reply.. but I am asking about the time taken to execute the query when I am submitting it.. When I give $mysql_query($sql_query); It should return the time it takes to execute not when it is executed...

[DonOfDen]

To get MYSQL query execution time in Query.. I want that in query itself rather than finding the time using PHP. Is there anyway?

[JaMaBing]

you can do this also in sql by getting the current time and diff them, but this doesnt return correct timings. Kapils solution is the right one. Otherwise you should have a look at this serverfault.com/questions/32102/hidden-features-of-mysql

[Rahul]

dont you think it should be echo $msc.' seconds'; // in MILLSECOND echo ($msc*1000).' milliseconds'; // in SECONDS

[ZurabWeb]

Dollar sign is not needed in front of mysql_query().