About 30 minutes ago I pushed out an update that has reduced the average database time per page load to less than a third of what it used to be. The change is specific to pages that show feed items (such as an RSS feed that we generate). These pages will now load much faster, and because of the overall reduced load on the database, everything should be faster.

The change was a relative easy one to make. What we were doing is querying the database for all the items that match a certain feed and date range. This could, potentially, be a few thousand items (more likely is a hundred or so), and since we generally only need the last 10, I load these into the web server for processing in blocks of 10. So what was happening is that we were executing the same find-me-these-items query a whole bunch of times, over and over, and throwing away most of the results except for the block of 10 we were interested in.

The new code uses an asynchronous SQL function called mysql_unbuffered_query(). This call generates the result set on the database and then holds onto them until we’re done. We can then pull items out, one at a time, until we’ve got the ones we want. The end result is that we do just one database search per feed instead of several or dozens. And the end result of THAT is the average database time per page query is now down below 5 seconds instead of up around 15 to 20.

As I said, this specific improvement will mostly affect pages that display feed items, but because of the reduced load on the database there should be a slight improvement across the board.

Advertisements