Menu
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
743
Now, go to the Scalyr dashboard menu and select MySQL. You will be able to see the log details of your MySQL, which includes the query time. This is a very simple and easy way to measure query time for a large number of MySQL queries.
Go to Menu >> Query >> Select Include client Statistics. Execute your query. In the results panel, you can see a new tab Client Statistics. Go to the Client Statistics tab to see the execution time.
also one other thing to note, if you realize you have made an error in your query and you want to go back and fix something enter \c at the prompt and then you will end your query and just go back to the prompt.
Is there a way to get the execution time of the last executed query in mysql? mysql has a builtin profiler. You can enable profiling by issuing set profiling=1; and use show profiles; to get execution times. 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.
If you want to log only those queries whose execution time exceeds 5 seconds, then set long_query_time to 5. Save and close the file. Now whenever a query takes more than 5 seconds to run, its details will be saved to log file. As your log file accumulates query execution time, it will become difficult to manually go through all this information.
In all probability, your query takes half a second to run, but it takes a lot more time to display all the results. The latter depends on the speed of your network and that of your computer and is not really relevant from a performance tuning perspective.
Then type an SQL statement, end it with ; , \g, or \G and press Enter. Typing Control+C interrupts the current statement if there is one, or cancels any partial input line otherwise. You can execute SQL statements in a script file (batch file) like this: On Unix, the mysql client logs statements executed interactively to a history file.
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/
152
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
38
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
