Databases Reference
In-Depth Information
Figure 6-1. Execution path of a query
In contrast, the response from the server usually consists of many packets of data. When
the server responds, the client has to receive the
entire
result set. It cannot simply fetch
a few rows and then ask the server not to bother sending the rest. If the client needs
only the first few rows that are returned, it either has to wait for all of the server's packets
to arrive and then discard the ones it doesn't need, or disconnect ungracefully. Neither
is a good idea, which is why appropriate
LIMIT
clauses are so important.
Here's another way to think about this: when a client fetches rows from the server, it
thinks it's
pulling
them. But the truth is, the MySQL server is
pushing
the rows as it
generates them. The client is only receiving the pushed rows; there is no way for it to
tell the server to stop sending rows. The client is “drinking from the fire hose,” so to
speak. (Yes, that's a technical term.)
Most libraries that connect to MySQL let you either fetch the whole result set and buffer
it in memory, or fetch each row as you need it. The default behavior is generally to fetch
the whole result and buffer it in memory. This is important because until all the rows
have been fetched, the MySQL server will not release the locks and other resources
