Caching OpenCart’s Category Counts

catsIf there’s one thing we could call out as the biggest opportunity to improve performance in opencart, it would be the underlying code that enables product counts for categories.

Opencart’s performance, once this problem is fixed, is actually quite good.  It’s undeserved reputation for being slow is almost always tied back to this one shortcoming.

On a stock opencart install, there’s a navigation widget that shows all the product categories, and the count of products within that category.

Category Counts
Category Counts

The architecture that enables those counts, unfortunately, is very heavyweight. It’s a method call named getTotalProducts() within opencart’s model file for products (/catalog/model/catalog/product.php).

Because opencart supports the idea of multiple “views” of a store, product counts are more complex than you would think. The count of products in a category could be different depending on:

  • the logged in user (perhaps “wholesale” buyers see more products, for example)
  • the selected language or currency (maybe some products only ship to selected regions)
  • administrator-selected options, like whether to show products from a sub-category in the parent category.
  • products that are marked as out of stock, or not yet available

What this boils down to is that calculating product counts per category, on the fly, results in a heavyweight SQL query being fired off…one query for EVERY category.   Our own testing showed it was adding 1 to 2 seconds of processing time.

If you happen to be okay with short-cutting some of the requirements, it’s fairly easy to make a single lightweight query that returns the product counts for all categories.   That’s what we did with our production site (budgetneon.com).   We were able to do that, since we only sell and ship within a single country, in a single language, and were okay with minor product count inaccuracies when a few items were out of stock.

While that works for us, it’s something of a hack, and not something of general use for the greater opencart community.

In searching for a more general purpose improvement, we noticed that opencart does come with a built-in caching mechanism.  It’s file based, and relatively simple to understand and leverage.  It seemed like it would be a fairly minor effort to cache the results of those multiple database calls.   Since most sites have the category widget on every page, the first uncached page retrieval would populate the cache for every other page.

We added some logging to see how often the getTotalProducts() method was being called on a single page, with a one line addition to the product model file:

1
2
3
4
5
6
7
public function getTotalProducts($data = array()) {
   // many lines not shown, skip to bottom of method
   $query = $this->db->query($sql);
   // added the line below
   $this->log->write("getTotalProducts: query is [$sql]");
   return $query->row['total'];
}
public function getTotalProducts($data = array()) {
   // many lines not shown, skip to bottom of method
   $query = $this->db->query($sql);
   // added the line below
   $this->log->write("getTotalProducts: query is [$sql]");
   return $query->row['total'];
}

We saw 21 queries per page load…one for every category, and page load time was around 1.3 seconds.

Adding in some caching, using opencart’s built-in cache mechanism was relatively painless…comment out 2 lines, add 8 new lines of code.  Once cached, page load time drops from 1.3 seconds to about 0.4 seconds.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
public function getTotalProducts($data = array()) {
   // many lines not shown, skip to bottom of method
   // comment out the two lines below
   //$query = $this->db->query($sql);
   //return $query->row['total'];
   // add 8 new lines of code, below:
   $cacheid='product.gettotalproducts.'.md5($sql).(int)$customer_group_id;
   $total=$this->cache->get($cacheid);
   if ($total === null ) {
       $query = $this->db->query($sql);
       $total = $query->row['total'];
       $this->cache->set($cacheid,$total);
   }
   return $total;
}
public function getTotalProducts($data = array()) {
   // many lines not shown, skip to bottom of method
   // comment out the two lines below
   //$query = $this->db->query($sql);
   //return $query->row['total'];
   // add 8 new lines of code, below:
   $cacheid='product.gettotalproducts.'.md5($sql).(int)$customer_group_id;
   $total=$this->cache->get($cacheid);
   if ($total === null ) {
       $query = $this->db->query($sql);
       $total = $query->row['total'];
       $this->cache->set($cacheid,$total);
   }
   return $total;
}

The code is straightforward. The lines that aren’t shown in the code snippet above is the code that dynamically creates SQL based on all the dependencies. We take that sql and get a unique md5 hash for it. That gives us a key that’s unique and tied to those dependencies…perfect for us as the key to store and retrieve from the cache. We then try to retrieve that from the cache. A cache miss gives us a null, indicating we need to run the query and store the result.

This, of course, doesn’t help with the performance of the first page load, but it helps tremendously on subsequent page loads…especially on a site with lots of products and/or categories. On our test sites, we see page retrieval times go from about 1.3 seconds down to 0.4 seconds once this heavyweight call is cached.

2 thoughts on “Caching OpenCart’s Category Counts

  1. Thanks for such a helpful post. I have implemented this and found that it improves a lot. Now I want to improve the admin side too. My product list all funciton is taking around 8+ secs to load. I have few thousands products and I am a learing developer. Can you please suggest any updates to admin side product model too?

    Many thanks
    Kamran

Leave a Reply

Your email address will not be published. Required fields are marked *


+ 5 = fourteen