Different network times with queries that return the same data

User, date Message
Written by marcio
1 year ago
Category: General
6 posts since Wed, 15 Feb 12 		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
Written by ansgar
1 year ago
4020 posts since Fri, 07 Apr 06 		Well, because there are other people using the same internet connection for downloading stuff or whatever.
Written by marcio
1 year ago
6 posts since Wed, 15 Feb 12 		Nops...I'm alone using the network and connecting with the MySQL server by tunel and always got the same decremented network time.
Written by marcio
1 year ago
6 posts since Wed, 15 Feb 12 		The same happen with all query optimization and I don't understand why!
Written by ansgar
1 year ago
4020 posts since Fri, 07 Apr 06 		Ok, sounds mysterious. However, noone here will be able to tell you something about the speed of your connection.
Written by marcio
1 year ago
6 posts since Wed, 15 Feb 12 		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!
Written by kalvaro
1 year ago
455 posts since Thu, 29 Nov 07 		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
Written by marcio
1 year ago
6 posts since Wed, 15 Feb 12 		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!
Written by kalvaro
1 year ago
455 posts since Thu, 29 Nov 07 		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.
Written by marcio
1 year ago
6 posts since Wed, 15 Feb 12 		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!
Written by kalvaro
1 year ago
455 posts since Thu, 29 Nov 07 		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.