MySQL (HeidiSQL): Using localhost but 10 seconds of query time is "network"?

Question

I am running mySQL queries using the heidiSQL editor. When it tells me my query time it will sometimes also include a network time:

Duration for 1 query: 1.194 sec. (+ 10.078 sec. network)

But it can't really be the network since everything is on my own computer?? Is that extra time something that would disappear with another setup or do I need to improve my query performance the usual way (rewriting/reworking)? It's hard for me to improve performance on a query when I'm not even sure what's causing the poor performance.

EDIT: Profiling info

I used this neat profiling sql: http://www.mysqlperformanceblog.com/2012/02/20/how-to-convert-show-profiles-into-a-real-profile/

Query 1:

Select count(*) from my_table_with_100_thousand_rows;

"Duration for 1 query: 0.390 sec." (This one did not show any network time, but almost .4 seconds for a simple count(*) seems a lot.)

STATE                    Total_R    Pct_R   Calls    R/Call
Sending data            0.392060    35.84   1    0.3920600000
freeing items           0.000214    0.02    1    0.0002140000
starting                0.000070    0.01    1    0.0000700000
Opening tables          0.000031    0.00    1    0.0000310000
statistics              0.000024    0.00    1    0.0000240000
init                    0.000020    0.00    1    0.0000200000
(shorter times not included)

Query 2:

select * from 4 tables with many rows, joined by primary_key-foreign_key or indexed column.

"Duration for 1 query: 0.156 sec. (+ 10.140 sec. network)" (the times below add up to more than the total?)

STATE           Total_R    Pct_R    Calls    R/Call
Sending data    16.424433    NULL    1    16.4244330000
freeing items   0.000390    NULL    1    0.0003900000
starting        0.000116    NULL    1    0.0001160000
statistics      0.000054    NULL    1    0.0000540000
Opening tables  0.000050    NULL    1    0.0000500000
init            0.000046    NULL    1    0.0000460000
preparing       0.000033    NULL    1    0.0000330000
optimizing     0.000028    NULL    1    0.0000280000
(shorter times not included)

Query 3: Same as query 2 but with count * instead of select *

"Duration for 1 query: 10.047 sec."

STATE         Total_R   Pct_R  Calls  R/Call
Sending data  10.050007 NULL   1     10.0500070000
(shorter times not included)

It seems to me that it includes network time in the "duration" if it has to display a lot of rows, but this does NOT mean that I can subtract this time if it doesn't have to display the rows. It's real query time. Does this seem right?

Answer 1

Old question! I'm pretty sure Heidi counts as "network time" the elapsed time --

• from receipt of the first response packet over the network
• to receipt of the last response packet in the result set.

So, for your SELECT COUNT(*) FROM big _f_table query the first packet comes back right away, and declares that there's a single column containing an integer.

The rest of that result set comes when the query engine is done counting the rows. So Heidi's so-called "network time" is the time to count the rows. That's practically instantaneous for MyISAM, and takes a while for InnoDB.

For your SELECT tons of columns FROM complex join the same thing applies. The first packet arrives when the query planner has figured out what columns will be in the result set. The last packet arrives when all that data has finally been transferred to Heidi over your computer's internal loopback (localhost) network.

It's like what you see in your browser devtools. The query time is analogous to the "time to first byte", and the "network time" is the time to deliver the rest of the result. Time to first byte is the query parsing / planning time PLUS the time to get enough information to send something for the result set metadata. Network time is the time to get the rest. If the query planner can stream the rows to you directly from the table storage you'll have a high proportion of network time. If, on the other hand, it has to crunch the data (for example with ORDER BY) you'll have a higher proportion of query time. But don't try to overthink this stuff. MariaDB and MySQL are very complex, with layers of caching and fetching. The way they satisfy queries is sometimes hard to figure out.