The functionality that counts the number of products in a category is a method inside the product model file (catalog/model/catalog/product.php). The reason it's slow is because it tries very hard to be accurate...accounting for products that are out of stock, products that have not yet reached their availability date, admin settings to whether to roll up subcategory products into the parent category, etc.
Assuming you have the category module widget on every page, opencart fires off one fairly heavyweight query for each category...on every page load. So, caching the results of the getTotalProducts() call speeds things up in a pretty big way. Once any page with the widget is viewed, the cache is used when other pages get loaded, for as long as the cache is valid (opencart's default cache lifetime is one hour).
Caching the results of the getTotalProducts() call using opencart's built in caching is pretty straightforward. Edit the catalog/model/catalog/product.php file, and go down the bottom of the getTotalProducts() method. For recent versions of OpenCart, you should see something like this:
Code: Select all
public function getTotalProducts($data = array()) {
if ($this->customer->isLogged()) {
// .... many lines of code...skip all this and go the bottom of the method:
// the 2 lines below are what will be changed
$query = $this->db->query($sql);
return $query->row['total'];
}
Code: Select all
public function getTotalProducts($data = array()) {
// .... many lines of code...skip all this and go the bottom of the method:
// note the 2 original lines of code commented out below.
//$query = $this->db->query($sql);
//return $query->row['total'];
// add these 8 lines before the closing brace of the method.
$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;
}
http://octurbo.com/caching-opencarts-category-counts/
If you're using chrome, compare the two demo sites (stock, and with this change) using the chrome developer tools-> network tab. Compare the load time of the html files. Note that you may have to load the site with this change twice...once to prime the cache, a second time to see the cached response time.