mysql command line return execution time?

Question

I'm working on a Linux host with mysql command. I have a script that runs batch mysql commands (like mysql -e "select...") and I wish to summarize execution time of each of the commands.

Is there a way to get mysql exec time from the command line?

For example, in mysql interactive mode, execution result comes with a time, like this:

mysql> select count(*) from trialtable;
+----------+
| count(*) |
+----------+
|     4000 |
+----------+
1 row in set (0.00 sec)

Can I get the same profile in command line?

Thank you

Is it fair game to throw time in front of the commands? Or do you want the real amount of execution time that MySQL consumes while computing the results? — sarnold
– sarnold, Commented Feb 22, 2012 at 3:03

andrew cooke · Accepted Answer · 2012-02-22 03:07:34Z

25

You can use

set profiling=1

and then, later,

show profiles

which will give a list of commands and times.

See http://dev.mysql.com/doc/refman/5.0/en/show-profiles.html

h/t http://ronaldbradford.com/blog/timing-your-sql-queries-2010-07-07/

answered Feb 22, 2012 at 3:07

andrew cooke

47.1k10 gold badges102 silver badges148 bronze badges

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

Comments

msb · Accepted Answer · 2017-05-12 21:46:24Z

17

You can invoke mysql with -vv, it will pretty-print similar to when you're in interactive mode:

$ mysql -vv -u myUser -pMyPass DBname -e 'select count(*) from mytable;'
--------------
select count(*) from mytable
--------------

+----------+
| count(*) |
+----------+
|  1068316 |
+----------+
1 row in set (0.00 sec)

Bye

If you're piping your queries, then it's -vvv:

$ echo 'select count(*) from mytable;' | mysql -vvv -u myUser -pMyPass DBname
--------------
select count(*) from mytable
--------------

+----------+
| count(*) |
+----------+
|  1068316 |
+----------+
1 row in set (1.34 sec)

Bye

Time's yours to grep. :D

answered May 12, 2017 at 21:46

msb

4,5684 gold badges35 silver badges41 bronze badges

1 Comment

geeves Over a year ago

This answer is particularly good when using the mysql client with ClickHouse, which supports the mysql tcp wire format. The reason being that set profiling=1 (as suggested in the accepted answer) does not work on ClickHouse. To be fair, the original question was about using the client with a MySQL server and not with a different DB server that happens to be mysql line wire compatible. However, for anyone looking to use the mysql client with ClickHouse or possibly other DB's that are mysql line wire compatible, this is the best bet.

Melebius · Accepted Answer · 2017-04-25 09:05:17Z

3

Here is the exact syntax for PHP.

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';

edited Apr 25, 2017 at 9:05

Melebius

6,7944 gold badges45 silver badges55 bronze badges

answered Apr 12, 2013 at 21:06

G-J

1,0682 gold badges18 silver badges32 bronze badges

Collectives™ on Stack Overflow

mysql command line return execution time?

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related