Opencart 3: Speed Up Category Product Count With Caching

Opencart has a known performance bottleneck where the category and filter loading times are significantly slower with the category product count enabled.

The php function responsible for the product counts is located in the \catalog\model\catalog\product.php file and is called getTotalProducts().

We can use Opencart’s cache to speed up the page loading times with only a small trade-off where the category product counts take a longer time to update.

In order to cache the product counts, we need to replace the return statement at the end of the function that can be found around line 519:

$query = $this->db->query($sql);
return $query->row['total'];

Replace with:

       $cacheid='product.gettotalproducts.'.md5($sql).'prc';
       $total=$this->cache->get($cacheid);
       if (!$total) {
           $query = $this->db->query($sql);
           $total = $query->row['total'];
           $this->cache->set($cacheid,$total);
       }
       return $total;

This uses an MD5 hash of the sql function to store the values and only runs the query on the database if the product count doesn’t already exist.