28.11 Improving Performance of MySQL Queries

The mysql_query function is perhaps the most popular function in PHP. If you're a MySQL user, you use it routinely to issue queries to the MySQL server and receive result sets. What you may not know is that when a query returns large result sets or queries large databases, mysql_query can be very inefficient.

In order to understand the reason for the inefficiency, you must understand how mysql_query works. When you issue a SELECT statement using mysql_query, PHP sends it to the MySQL server. The MySQL server parses it, creates an execution plan, and starts to iterate over the table rows, looking for valid results. Every time it finds a valid result, the server sends it back over the network to the client. On the client side, PHP appends each row to a buffer, until the server sends a message that acknowledges that no rows remain. When this happens, mysql_query returns control to the PHP application and allows it to iterate over the result buffer.

The performance problem arises when we deal with large result sets or when we're querying very big databases. In such cases, the time that passes from receiving the first result row and receiving the last one can be quite long. Even though our client is idle and is virtually doing nothing, we cannot use this time to begin processing the results. We have to wait until the server sends the very last row, and only after we get control back can we process the results. If we could start processing the result rows as soon as they start arriving instead of having to wait for the last row, performance would improve significantly. As usual, PHP doesn't disappoint us.

In addition to mysql_query, PHP offers an additional version of the function, named mysql_unbuffered_query. The API for the two functions is identical, but mysql_unbuffered_query does not buffer the result rows before returning control to the PHP application. Instead, it returns control to PHP as soon as it issues the query successfully. Each time we fetch a row, the MySQL module attempts to read the next row from the server and returns control to the application as soon as it fetches the row. That way, we can process the rows as they arrive instead of having to wait for the entire result set to become available.

If unbuffered queries are so good, why does PHP even let you use regular, buffered queries? Unfortunately, there's a good reason for that—unbuffered queries are not always a good idea. If the server sends the rows faster than the client reads them, the server will keep the relevant tables locked for more time than necessary. SQL statements needing to write to the table must wait until the read operation finishes. Since this may result in a huge performance degradation for pages that make changes to the database, using unbuffered queries is recommended only if the amount of processing your pages perform on each row is sufficiently small or if updates are infrequent.