How to test speed a query without result in MySQL

Question

I have queries that return thousands of results, is it posible to show only query time without actual results in MySQL console or from command line?

is this enough on cmd: `date && mysql < your_script.sql && date` ? — rabudde, May 29 '12 at 11:11
Is it posible to display time in seconds not datetimes? (the same as after the query in mysql console but without results). — jcubic, May 29 '12 at 11:22
To display time in seconds for a process, under Unix at least, use the `time` command: `/usr/bin/time -f "%e" -- mysql < your_script.sql > /dev/null` — Patrice Levesque, Mar 20 '14 at 15:55

score 7 · Accepted Answer · edited May 23 '17 at 12:16

7

Use SET profiling = 1; at command prompt.

It's not possible to get the execution time without getting result or getting sql executed.

edited May 23 '17 at 12:16

Community

answered May 29 '12 at 11:15

Romil Kumar Jain

score 5 · Answer 2 · answered Oct 27 '18 at 09:21

If you're using Linux.

time mysql your_db_name -u'db_user_name' -p'your_password' < query.sql > /dev/null

The output will look something like this:

real    0m4.383s    
user    0m0.022s    
sys     0m0.004s

the "real" line is what you're looking at. In the above example, the query took 4.38 seconds.

Obviously, entering your DB password on the command line is not such a great idea, but will do as a quick and dirty workaround.

2 Answers2