Different network times with queries that return the same data

[expired user #6226]'s profile image [expired user #6226] posted 12 years ago in General Permalink
I have a inicial query return status:
/* 0 rows affected, 4,991 rows found. Duration for 1 query: 0.717 sec. (+ 12.714 sec. network) */

Optimizing tables with indexes, I got to the same query:
/* 0 rows affected, 4,991 rows found. Duration for 1 query: 0.078 sec. (+ 1.779 sec. network) */

I have exactly the same query, the same number of records and columns and the data also are exactly identical. Why I got decremented so much the network time?

Thanks a lot!
Marcio
ansgar's profile image ansgar posted 12 years ago Permalink
Well, because there are other people using the same internet connection for downloading stuff or whatever.
[expired user #6226]'s profile image [expired user #6226] posted 12 years ago Permalink
Nops...I'm alone using the network and connecting with the MySQL server by tunel and always got the same decremented network time.
[expired user #6226]'s profile image [expired user #6226] posted 12 years ago Permalink
The same happen with all query optimization and I don't understand why!
ansgar's profile image ansgar posted 12 years ago Permalink
Ok, sounds mysterious. However, noone here will be able to tell you something about the speed of your connection.
[expired user #6226]'s profile image [expired user #6226] posted 12 years ago Permalink
Yes, it is very mysterious and always happen with all query optimization, I just not undesrtand this time!. I have a high speed connection and it is not a problem. Some idea?? Thanks a lot!
kalvaro's profile image kalvaro posted 12 years ago Permalink
My educated guess is that indexes allow faster row retrieval in your case. The manual seems to suggest the possibility:

http://dev.mysql.com/doc/refman/5.5/en/mysql-indexes.html
[expired user #6226]'s profile image [expired user #6226] posted 12 years ago Permalink
Hi kalvaro, It's normal that query be faster, what I don't understand and this is my question, is why the network time is also faster if the result data is exactly the same!??

thanks!
kalvaro's profile image kalvaro posted 12 years ago Permalink
You are assuming that MySQL does not send a single row until it has found and retrieved the complete result set from disk and stored it in the server's RAM. I don't think that's the general procedure.
[expired user #6226]'s profile image [expired user #6226] posted 12 years ago Permalink
Hi kalvaro,

I'm not assuming that, I just wanna understand what "Network time" realy means...if I optimize some query (by table index or whatever), why the network time is also faster if the result data is exactly the same!?

Regards!
kalvaro's profile image kalvaro posted 12 years ago Permalink
I'm having serious difficulties in understanding your exact doubt, really. When you send a SELECT query to the MySQL server, it does two things (among others):

1. Identify and sort the rows that belong to the result set.
2. Extract from the database the appropriate columns for those rows and get them sent to the client.

I don't know where HeidiSQL grabs the figures from, but the MySQL server provides the execution time for those both steps. The important bit is that the "network time" counter probably starts counting when the first row is about to be sent and stops when the last one has been sent——it does not pause every microsecond when the network card is not physically transmitting bytes——it does not pause when the client requests next row and waits for the server to acknowledge.

If you made a road trip from 9:00 to 12:00 it took three hours, even if you were having lunch from 10:00 to 11:00.

Please login to leave a reply, or register at first.