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. enter image description here

enter image description here

I found some thing Check this

Community · Accepted Answer · 2016-01-26 21:28:27Z

11

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

edited Jan 26, 2016 at 21:28

CommunityBot

11 silver badge

answered May 8, 2013 at 7:27

kapil

1625 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

DonOfDen Over a year ago

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 Over a year ago

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 Over a year ago

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 Over a year ago

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

ZurabWeb Over a year ago

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

Abhijit · Accepted Answer · 2014-07-05 11:02:02Z

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

answered Jul 5, 2014 at 11:02

Abhijit

1141 silver badge10 bronze badges

Collectives™ on Stack Overflow

To get MYSQL query execution time in Query

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related