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?

Arnulfo

is this enough on cmd: date && mysql < your_script.sql && date ? – Inflection

dont forget to redirect output to /dev/null – Boreas

Is it posible to display time in seconds not datetimes? (the same as after the query in mysql console but without results). – Arnulfo

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 – Eben

Answer 1

Use SET profiling = 1; at command prompt.

Refer for more details

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

See why we can not get execution time without actual query execution

Acutance

Answer 2

If you're using Linux.

1. Create a file query.sql
2. Enter the query to test into it. e.g. select * from table1
3. Run this command:

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.

Saied