ID #1129

How shall I understand the 'query execution time' that SQLyog gives me?


SQLyog displays 'query execution time' for each query in three different places: The STATUS bar/line, the MESSAGES tab and the HISTORY tab.

Let us explain what they are and how they are used (note: This is as of SQLyog version 7.1)

To get this TIME information SQLyog makes use of three different TIMESTAMPs

1) First TIMESTAMP (T_1): this is collected when you click 'execute' and the query is sent to the server.

2) Second TIMESTAMP (T_2): After receiving it, the server queues it, executes it and returns a status flag (whether execution was successful or not). When SQLyog receives this status flag the second TIMESTAMP is collected.

3) Third TIMESTAMP (T_3): After receiving, formatting and printing those data to the screen the third TIMESTAMP is collected.

The 3 above timestamps are used for calculations:

* execution time: T_2 - T_1
* transfer time: T_3 - T_2
* total time: T_3 - T_1

In MESSAGES tab all three calculated times are displayed
In HISTORY tab only the calculated execution time is displayed
In proces line total time and execution time is displayed. In case you execute multiple statements at a time SQLyog will 'aggregate' the information for all queries executed.

Note that all those TIMESTAMPs are collected from the client system. With a reasonable fast connection the data calculated as described are pretty close to the true exection time. Note that the transfer time displayed will contain the very small (mostly neglible) overhead caused by internal SQLyog processing. A more important source of inaccuracy (in particular with large BULK INSERT statements as well as LOAD DATA statements) is however that the time for any 'upstream' data transfer will be contained in the 'execution time' displayed.

Starting from version 5.0.37 MySQL supports 'query profiling' server-side. SQLyog ULTIMATE edition can utilize this with its 'Query Profiler' feature. But the 'execution time' display as per the description above still appears independent of the Query Profiler output - also in SQLyog ULTIMATE.

Also there is a modification for HTTP-tunneled connections: With HTTP-tunnel note that only the total time displayed in all three places as the tunneller file is not able to collect timestamps from neither remote or local system. And it does not make sense in our opinion to include HTTP-related transfer overhead in the information displayed. As a consequence the time displayed in HISTORY for HTTP-tunnelled connections is the total time.

Tags: -

Related entries:

You can comment this FAQ