A Solution - Caching - MySQL

The bottleneck in many modern web sites is the database. This may seem like a daunting problem because database hardware can be expensive and rearchitecting the application with a different database structure can require a large time commitment. Luckily, most of the data presented on the internet is ideal for caching.

Let us consider our example set of queries from before: user credentials, session information, system notifications, new messages, latest headlines, configuration information, and content areas. Of those, latest headlines, configuration information, system notifications, and content areas are not likely to change very often or vary per user. Digging down further, we’ll consider news headlines specifically and build up a solution to dramatically reduce the database queries to this table.

For a typical website, news headlines will be generated with a query like this:

SELECT *

FROM news

WHERE category_id=21 AND status=1

ORDER BY publish_date DESC

LIMIT 5;

Each time this query is executed, MySQL will (assuming the query cache is not enabled, which is another issue) first parse the query, decide how to locate the data, read the table index, filter out rows by status or category_id, order the remaing rows, and return the first five. With proper indexes, this query may take a fraction of a second, but enough queries will eventually bog down the database. And 99% of the time, that query will return the same data it returned the last time.

So here, we’ve found a query that runs on nearly every page load, is the same for every user to the site, and returns a relatively small result. Five news headlines, perhaps the links to the full article, the URL for a photo, and a published date can likely fit in under one kilobyte of memory.

Now that we’ve identified a candidate for caching, we simply need a place to cache it. PHP and the Linux/Apache environment provide numerous ways to do this, but we’ll focus on one solution here, MemCached.