Query Execution Time == 0ms

[rrkramer]

On MySql 3.23 and 4.1 (haven't tried other versions) the label at the bottom of the screen that shows time and rows always reports 0 ms, no matter how long the query really takes. The connections are direct (non-tunnel).

It seems to me that the timer worked sometime in the past. Could it be made to work again?

Richard

[ronjeremy_69]

I noticed this a few days ago also — although i didnt have enough time to investigate too deeply my observations were that the statusbar number showed 0ms for most short queries, something other than 0ms for longer queries, but in no case did it ever match up with the query time shown in the “history” tab. I remember it working properly in pervious versions also (Im currently using 5.22 Beta)

[rrkramer]

More info – it seems that sometimes the timer works, sort of.

I ran the two queries with two limits below against databases on two machines and two table in each database.

Machine A is on a local lan, 100 BaseT connecection, but the db server is old and slow.

Machine B is over the internet, 15 hops, average ping ~50 ms, max about 400 ms over a 5 minute span.

Table 1 consists of 3 columns, average length 44 bytes.

Table 2 consists of 62 columns, average length 527 bytes.

The “?” in the queries below represent the two tables and two limit options.

Query A:

SELECT LEFT(xxx,3) as s,COUNT(*) FROM table? GROUP BY s LIMIT ?;

Query B:

SELECT * FROM table? WHERE yyy=”” ORDER BY xxx DESC LIMIT ?;

The two limits were 10 and 1000.

Code:

Machine A Machine B
Query Table Limit MySql(secs) SQLyog (ms) MySql(secs) SQLyog (ms)
A 1 10 1.88 0 0.25 0
A 1 1000 0.89 0 0.27 0
A 2 10 0.28 0 0.20 0
A 2 1000 0.30 0 0.20 0
B 1 10 1.80 0 0.46 0
B 1 1000 1.81 0 0.47 406
B 2 10 0.73 0 0.48 0
B 2 1000 0.87 110 0.59 1547

So one conclusion is that the SQLyog timer results are not strongly related to either the mysql reported times, nor the wall clock times. The timer data seems most strongly related to the amount of data returned.

Richard

[peterlaursen]

Please read this FAQ:

http://webyog.com/faq/8_147_en.html

[ronjeremy_69]

Im not quite sure the reason this was changed?

One of the main reasons I use SqlYog is to prototype and find optimial queries that will be part of an application on the server itself. From a developer's standpoint, really I could care less knowing the amount of time it takes to actually transfer data from the mysql server to my windows PC and display it. A number showing me milleseconds elapsed depending on the kind of network connection I have and how fast my client PC can display data in SqlYog is irrelavent.

What is most relavent is how fast my queries will be executing on the server itself. In the past, it was great to be able to press F5 on a few variants of the same query and see the milliseconds elapsed in the STATUS bar to decide which query was optimal. It is inconvenient to have to flip back and forth to the History tab between each F5. I cant think of any reason that knowing the time elapsed sending data over the network could possibly be more important than knowing the time the query itself takes to execute, especially when 90% of the time on a LAN it will be 0ms (unless the resultset is large)?

I propose either changing it back to the way it used to be, or having it so there are both indicators in the STATUS bar. Theres plenty of room down there:)

[peterlaursen]

Quote:

What is most relavent is how fast my queries will be executing on the server itself.

This is simply not possible and never was. The server does not send this information. Also the 'mysql' command line client gets this information from the client machine.

I also am not aware of any intentional change here, but let us discuss.

[peterlaursen]

After refreshing my memory I must change a little … 🙁

1) We recently fixed a regular bug. That was that the status line always displayed 0 (zero) when MESSAGES tab was active.

2) At the same time we changed so that the status line always displayed the same no matter what tab was active.

Basically the situation now is this:

1) HISTORY TAB displays the server execution time plus a little transmission 'overhead' to send the query and to get the status flag from the Server.

2) STATUS LINE + MESSAGES TAB displays (what is very close to) the transmission time for the RESULTSET plus a little 'overhead' for the client to format and display data.

Now it would be no big deal to display both in the status bar with (for instance) a slash in between (like “5/37 ms taken”), or we could display the sum of the two (like “5/42 ms taken”). So let us hear what people want (and the argumentation not at least)!

[ronjeremy_69]

peterlaursen wrote on Dec 13 2006, 11:56 PM:

This is simply not possible and never was. The server does not send this information. Also the 'mysql' command line client gets this information from the client machine.

I also am not aware of any intentional change here, but let us discuss.

To test, I use the command-line mysql client to query a 200,000 row table with “SQL_NO_CACHE” and “limit 1” on a non-indexed column, and the client reported the query completed in 0.41 seconds. Then on a windows machine i ran the same query on SQLyog v5.22 Beta 2 and the STATUS bar reported 0ms. then i uninstalled and installed 5.12 and the STATUS bar reported 431ms, which is pretty much exactly what the command line reported (and how it should be).

[ronjeremy_69]

peterlaursen wrote on Dec 14 2006, 12:46 AM:

So let us hear what people want (and the argumentation not at least)!

It would be most useful to have the STATUS bar number reflect the query execution time on the server and be as close to what you would get with the mysql command-line as possible. As such, the STATUS bar number should mirror what you would see in the History tab. This is how i cast my vote, and i believe rrkramer would as well!

[FireStorm9]

I'll chime in on this one.

How about both? Would it be too difficult to add both times?

Something like:

Q: 5ms D: 15ms

Or the simpler way that Peter mentioned earlier:

5ms/15ms

That would be great, as I also strive to get every last millisecond out of the queries AND the data retrieval in my applications.

Cheers,

[peterlaursen]

Starting from next release (will likely be named beta3) we will let the STATUS LINE display be identical to HISTORY. That is as close to SERVER EXECUTION TIME as we can get.

This need not be the final solution, so just keep on discussing. However we realize that when working on 'localhost' and not very big queries STATUS LINE display does not tell much at the moment!

[peterlaursen]

Actually there was a detail that none of us had thought about. That is that we can have multiple queries.

So the solution for 5.22 will be.

1) When a RESULT tab is active status line will display the 'HISTORY-time' of that query.

2) When some other tab (not a RESULT tab) is active status line will display like MESSAGES tab does.

this need not be the final solution so just go ahead with the discussion …

[Author]

Posts