How do PHP/MySQL database queries work exactly?

Question

I have used MySQL a lot, but I always wondered exactly how does it work - when I get a positive result, where is the data stored exactly? For example, I write like this:

$sql = "SELECT * FROM TABLE"; 
$result = mysql_query($sql);
while ($row = mysql_fetch_object($result)) {
    echo $row->column_name;
}

When a result is returned, I am assuming it's holding all the data results or does it return in a fragment and only returns where it is asked for, like $row->column_name?

Or does it really return every single row of data even if you only wanted one column in $result?

Also, if I paginate using LIMIT, does it hold THAT original (old) result even if the database is updated?

Answer 1

I think you are confusing the two types of variables you're dealing with, and neither answer really clarifies that so far.

$result is a MySQL result object. It does not "contain any rows." When you say $result = mysql_query($sql), MySQL executes the query, and knows what rows will match, but the data has not been transferred over to the PHP side. $result can be thought of as a pointer to a query that you asked MySQL to execute.

When you say $row = mysql_fetch_object($result), that's when PHP's MySQL interface retrieves a row for you. Only that row is put into $row (as a plain old PHP object, but you can use a different fetch function to ask for an associative array, or specific column(s) from each row.)

Rows may be buffered with the expectation that you will be retrieving all of the rows in a tight loop (which is usually the case), but in general, rows are retrieved when you ask for them with one of the mysql_fetch_* functions.

If you only want one column from the database, then you should SELECT that_column FROM .... Using a LIMIT clause is also a good idea whenever possible, because MySQL can usually perform significant optimizations if it knows that you only want a certain group of rows.

Answer 2

The details are implementation dependent but generally speaking, results are buffered. Executing a query against a database will return some result set. If it's sufficiently small all the results may be returned with the initial call or some might be and more results are returned as you iterate over the result object.

Think of the sequence this way:

1. You open a connection to the database;
2. There is possibly a second call to select a database or it might be done as part of (1);
3. That authentication and connection step is (at least) one round trip to the server (ignoring persistent connections);
4. You execute a query on the client;
5. That query is sent to the server;
6. The server has to determine how to execute the query;
7. If the server has previously executed the query the execution plan may still be in the query cache. If not a new plan must be created;
8. The server executes the query as given and returns a result to the client;
9. That result will contain some buffer of rows that is implementation dependent. It might be 100 rows or more or less. All columns are returned for each row;
10. As you fetch more rows eventually the client will ask the server for more rows. This may be when the client runs out or it may be done preemptively. Again this is implementation dependent.

The idea of all this is to minimize roundtrips to the server without sending back too much unnecessary data, which is why if you ask for a million rows you won't get them all back at once.

LIMIT clauses--or any clause in fact--will modify the result set.

Lastly, (7) is important because SELECT * FROM table WHERE a = 'foo' and SELECT * FROM table WHERE a = 'bar' are two different queries as far as the database optimizer is concerned so an execution plan must be determined for each separately. But a parameterized query (SELECT * FROM table WHERE a = :param) with different parameters is one query and only needs to be planned once (at least until it falls out of the query cache).