Data transfer between web/db server

Question

I have a PHP script on webserver 1 located in country A. I have a DB server located in country B.

The php script queries a large table from the DB server, groups the results, and inserts them back into the DB server (to another table). This is done by a single query (INSERT INTO SELECT...)

My question here is, does the data actually transfer between the web/db server? E.g. is this using GB's of bandwidth on both servers?

Answer 1

If you never deal with any retrieved data from DB server, then the query won't send any data to web server 1. Basically, if you just run the query, the only data that's sent is the text of the query (e.g. INSERT INTO SELECT...), which is probably just a few bytes, and then the response, which is just a success/fail value. That's it.

To say it another way, the data from the SELECT part of your INSERT INTO SELECT query is all dealt with on the DB server, it's never sent to webserver 1.

Even if you did run a SELECT query on a remote databse, you wouldn't get all of the results back. You actually get a resource. This resource is a reference to a set of data still on the remote database. When you do something like fetch_row on that resource, it fetches the next row. At that point, the data is transferred.

You can test this by monitoring the memory usage of your PHP script at various points in it's execution using memory_get_usage. Try:

echo "Memory before query:  " . memory_get_usage() . "\n";

$result = $mysqli->query("your select query");

echo "Memory after query:  " . memory_get_usage() . "\n";

$data = array();

$i=1;
while ($row = $result->fetch_row()) {
    $data[] = $row;
    echo "Memory after reading row " . $i++ . ": " . memory_get_usage() . "\n";
}

You should see a very small increase of used memory after your SELECT, and then a steady increase as your iterate over the results.