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?

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

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.