OpenCart Database Cache

I recently developed a plugin for OpenCart, the plugin (excuse the cheese) is called: Super FAST Database Cache.

I’ve been using OpenCart for years, and prefer it over Magento for a dedicated shopping cart (I’m quite fond of WooCommerce too). I decided to bundle up this plugin as I wrote it for a website that required a bit of a better caching solution than the out-of-the-box caching that ships with OpenCart.

The plugin is purely a database cache. It replaces the default MySQL driver, rather than the somewhat dirty vqMod implementations that seem to be the norm. The only drawback is that different OpenCart versions have different class names and locations for the drivers, even on minor version changes.

So how does it work? The database driver will MD5 hash the query, and check if a cache file exists with that name, if so (and it hasn’t expired) then that’s used, otherwise the query runs as per normal. This is only the case for SELECTs, of course.

The site I implemented it on originally has over 1,000 categories and tens of thousands of products. OpenCart is quite inefficient on larger product and category bases, as it implements SELECT DISTINCT queries, as well as requesting all categories with a product count (COUNT(*)) on every page load, so it can quickly get out of hand. The site in question went from an average page load of 45-60 seconds to around 1-2 seconds. It could be brought down further with the use of memcache or a reverse proxy, but that’s a little overkill in this case.

The admin area uses a different driver. Some nice to-dos could be to force a fresh query by passing an additional parameter, and to remove the expiration check and instead have a cron job run periodically to flush and resync the cache – this would prevent a user periodically getting a slow load.