How can I ONLY get the query execution time on the command line without waiting till the data is displayed?

Question

I am testing and comparing execution times of queries with big result sets using the command line.

It takes too long to display the result set! Is there any way to see only the query execution time without waiting till all of the result set is displayed?

• mustaccio: I guess you will need to define what "query execution time" means to you. Sending the result set to the client and processing it (e.g. formatting and displaying it on the terminal) might very well be a part of "execution". What is the purpose of this " testing and comparing"?
• Andranik Markaryan: I am testing how different indexes affect on same query execution speed, so I don’t need to take to consideration time that command line spends on displaying the result set.
• mustaccio: Execution plans should tell you that.
• JYOTI RAJAI: which MySQL Version you are using ?
• NikitaSerbskiy: is it ok to send output to file?

Answer 1

Solution for Getting only Execution time of Query without printing result set :

Query :Select C1 , C2, C3 from Table where C3='1'; -- 40571 rows in set (0.079 sec)

1. Get Count of query :

   Do : Select count(*) from (Select C1 , C2, C3 from Table where C3='1') as X;

   Time : 0.035 sec

2. Redirecting output to /dev/null :

   Do : time mysql -u'user' -p'password' Database < query.sql > /dev/null

   Time :

 real 0m0.180s
 user 0m0.112s
 sys 0m0.005s

3. Using mysqlslap :

   Do :

vi query1.sql // Addin the query.
mysqlslap -u'user' -p'password' --query="query1.sql" --delimiter=";" --concurrency=1 >> output.log

Time : 
mysqlslap: [Warning] Using a password on the command line interface can be insecure.
Benchmark
 Average number of seconds to run all queries: 0.080 seconds
 Minimum number of seconds to run all queries: 0.080 seconds
 Maximum number of seconds to run all queries: 0.080 seconds
 Number of clients running queries: 1
 Average number of queries per client: 1

According to me ideal is 3rd solution using mysqlslap

• Andranik Markaryan: Thank you for answer and sorry for late reply, I also prefer mysqlap solution, but I can't understand why execution time shown when running query in cmd mismatch time shown by mysqlslap. I executed this query select * from test_users_s; 10 times in cmd, and got stable result: (0.019, 0.019, 0.019, 0.019, 0.019, 0.021, 0.020, 0.021, 0.019, 0.019), then I executed following command mysqlslap -u root -p --create-schema="mysql_test" --query="select * from test_users_s;" 10 times and get this not stable results: (0,031, 0,031, 0,031, 0,015, 0,015, 0,031, 0,015, 0,032, 0,031, 0,016).
• Andranik Markaryan: Maybe I am doing smth wrong? Only when running query with mysqlslap with 10-20 iterations I get avg result near to result of cmd: Average number of seconds to run all queries: 0.018 seconds Minimum number of seconds to run all queries: 0.015 seconds Maximum number of seconds to run all queries: 0.031 seconds Number of clients running queries: 1 Average number of queries per client: 1
• JYOTI RAJAI: In direct cmd prompt, you are already connected to server & when you frequently re-executed you get output from cache which why time is stable. But in mysqlslap there are so many other factors came in picture everytime new connection , querycache etc.