How to Get Query Execution Time in MySQL

It is good to keep track of execution time of SQL queries in MySQL. It will help you identify long running queries that cause bottlenecks. In this article, we will learn how to get query execution time in MySQL.

How to Get Query Execution Time in MySQL

There are two ways to measure query execution time in MySQL – for single queries and for multiple queries.

Measure Query Time for Single Queries

When you run SQL queries in MySQL console, it shows the query execution time after the result is displayed. But you won’t get this information if you are running queries on a server-side program. In this case, you need to run the following query before you run your SQL query.

set profiling=1

Next, run your SQL query. You may run multiple queries one after the other if you want.

select * from data;

Finally, run the following command.

show profiles;

You will see the following kind of output.

+----------+------------+---------------------+
| Query_ID | Duration   | Query               |
+----------+------------+---------------------+
|        1 | 0.00015425 | select * from data  |
|        2 | 0.00023375 | SELECT DATABASE()   |
|        3 | 0.14587650 | show databases      |
|        4 | 0.00013400 | SELECT DATABASE()   |
|        5 | 0.08448550 | select * from sales |
+----------+------------+---------------------+

As you can see, MySQL logs and displays the query execution time of each query run after you profiling=1.

Measure Query Execution Time for Multiple Queries

The above approach works best for one or more queries. But if your application or website runs a lot of queries, especially of transactional nature, then it is difficult to keep track of query execution time of each query. For this purpose, MySQL provides a slow query log which allows you to specify a threshold to classify queries as slow, and record execution time of only those queries that exceed this threshold.

By default, slow query log is disabled in MySQL. To enable it, open MySQL configuration file and add the following line to it.

slow_query_log

Additionally, you can also mention the location of query log file with the following command.

slow_query_log_file=<path of the file>

Finally, add the following line to log all queries in your slow query log.

long_query_time=0

If you want to log only those queries whose execution time exceeds 5 seconds, then set long_query_time to 5.

long_query_time=5

Save and close the file. Now whenever a query takes more than 5 seconds to run, its details will be saved to log file.

As your log file accumulates query execution time, it will become difficult to manually go through all this information. For this purpose, you may want to use a log management tool, that automatically parses MySQL log file and helps you quickly find the information you are looking for.

In this article, we have learnt how to measure query execution time in MySQL.

How to Get Query Execution Time in MySQL