How to check how long MySQL query is taking?

Question

When I run a query, I don't want it to print to the console. I just want to see the time.

9166 rows in set (0.90 sec)

Thats all I want to see, instead of it printing everything.

Answer 1

Try this simple example

mysql> set profiling=1;
mysql> select count(*) from comment;
mysql> select count(*) from message;
mysql> show profiles;

+----------+------------+------------------------------+
| Query_ID | Duration   | Query                        |
+----------+------------+------------------------------+
|        1 | 0.00012700 | select count(*) from comment |
|        2 | 0.00014200 | select count(*) from message |
+----------+------------+------------------------------+
2 rows in set (0.00 sec)

Answer 2

You can write your query inside sub-query with COUNT to do the trick as:

SELECT COUNT(1)
FROM ( SELECT * FROM your_table WHERE ...) a

It may slow down your query slightly, as it's doing COUNT also but I think its negligible.

For measuring performance of query you can turn on PROFILES in MySQL as:

SET profiling = 1;

For more details about PROFILES see here.