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

Answer 1

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/

Answer 2

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