Fix opencart loading very slow

November 14, 2013 Site optimization

→ Are you a new visitor? Please visit the page guidance for new visitors ←

Fix opencart loading very slow

Its been awhile since I wrote a site optimization tip, maybe the reason why is that I’m probably out of ideas of what to write about. Even now, I had a client that was using OpenCart with a large number of products in his catalog and causing slow loading on his site. What was causing the load was actually the products count for each category in the menu. Yes, the menu actually slows down opencart dramatically. From a 4+ seconds time load, I got it down to 1.4 seconds or less. Anyway, this tutorial will be to show you how you can fix your site with opencart loading very slow.

Now don’t get me wrong, I do like this script, actually I like it better than prestashop. I don’t really know for certain why though, maybe due its simplicity. However the developers on this script should think about setting an option for disabling the products count for each category in our menu.

The cause of the slow down

The cause of the slowdown is a similar query that will always be triggered when you’re loading the page.

SELECT COUNT(DISTINCT p.product_id) AS total
FROM oc_category_path cp 
LEFT JOIN oc_product_to_category p2c ON (cp.category_id = p2c.category_id) 
LEFT JOIN oc_product p ON (p2c.product_id = p.product_id) 
LEFT JOIN oc_product_description pd ON (p.product_id = pd.product_id) 
LEFT JOIN oc_product_to_store p2s ON (p.product_id = p2s.product_id) 
WHERE pd.language_id = '1' AND p.status = '1' AND p.date_available <= NOW() AND p2s.store_id = '0' AND cp.path_id = '269'

SELECT COUNT(DISTINCT p.product_id) AS total

FROM oc_category_path cp

LEFT JOIN oc_product_to_category p2c ON (cp.category_id = p2c.category_id)

LEFT JOIN oc_product p ON (p2c.product_id = p.product_id)

LEFT JOIN oc_product_description pd ON (p.product_id = pd.product_id)

LEFT JOIN oc_product_to_store p2s ON (p.product_id = p2s.product_id)

WHERE pd.language_id = '1' AND p.status = '1' AND p.date_available <= NOW() AND p2s.store_id = '0' AND cp.path_id = '269'

Fixing the issue

Obviously I’m not the *genius* inventing the wheel, someone was kind enough to also create an extension for disabling the products count.

You can download it here.

If you don’t want to use an extension and want to manually do this yourself, you can follow the steps below. There 3 files you need to be careful about, these are:

catalog/controller/common/header.php — For the menu in your header
catalog/controller/product/category.php — For subcategories when clicking on one main category
catalog/controller/module/category.php — For the left side menu

These 3 so far I found to be the cause of this. Now what you need to do, for example in /catalog/controller/common/header.php, look for similar lines like below:

					$product_total = $this->model_catalog_product->getTotalProducts($data);

					$children_data[] = array(
						'name'  => $child['name'] . ($this->config->get('config_product_count') ? ' (' . $product_total . ')' : ''),
						'href'  => $this->url->link('product/category', 'path=' . $category['category_id'] . '_' . $child['category_id'])
					);

$product_total = $this->model_catalog_product->getTotalProducts($data);

$children_data[] = array(

'name' => $child['name'] . ($this->config->get('config_product_count') ? ' (' . $product_total . ')' : ''),

'href' => $this->url->link('product/category', 'path=' . $category['category_id'] . '_' . $child['category_id'])

);

Replace these with:

					//$product_total = $this->model_catalog_product->getTotalProducts($data);
					$product_total = 0;				
					$children_data[] = array(
						//'name'  => $child['name'] . ($this->config->get('config_product_count') ? ' (' . $product_total . ')' : ''),
						'name'  => $child['name'],
						'href'  => $this->url->link('product/category', 'path=' . $category['category_id'] . '_' . $child['category_id'])
					);

//$product_total = $this->model_catalog_product->getTotalProducts($data);

$product_total = 0;

$children_data[] = array(

//'name' => $child['name'] . ($this->config->get('config_product_count') ? ' (' . $product_total . ')' : ''),

'name' => $child['name'],

'href' => $this->url->link('product/category', 'path=' . $category['category_id'] . '_' . $child['category_id'])

);

What we are doing here is commenting the method to get the total products from our category and initializing the variable in case its needed with 0 (zero). Next in the children_data array, this is for subcategories, we comment the part where we declare the name of the subcategory and removing the part with showing the products count, as its no longer needed.

For /catalog/controller/module/category.php, will be similar but you have a bit more information, for example:

				$product_total = $this->model_catalog_product->getTotalProducts($data);
				$total += $product_total;

				$children_data[] = array(
					'category_id' => $child['category_id'],
					'name'        => $child['name'] . ($this->config->get('config_product_count') ? ' (' . $product_total . ')' : ''),
					'href'        => $this->url->link('product/category', 'path=' . $category['category_id'] . '_' . $child['category_id'])	
				);

$product_total = $this->model_catalog_product->getTotalProducts($data);

$total += $product_total;

$children_data[] = array(

'category_id' => $child['category_id'],

'name' => $child['name'] . ($this->config->get('config_product_count') ? ' (' . $product_total . ')' : ''),

'href' => $this->url->link('product/category', 'path=' . $category['category_id'] . '_' . $child['category_id'])

);

replace it with:

				//$product_total = $this->model_catalog_product->getTotalProducts($data);
				//$total += $product_total;
				$total = 0;
				$product_total = 0;

				$children_data[] = array(
					'category_id' => $child['category_id'],
					//'name'        => $child['name'] . ($this->config->get('config_product_count') ? ' (' . $product_total . ')' : ''),
					'name'        => $child['name'],
					'href'        => $this->url->link('product/category', 'path=' . $category['category_id'] . '_' . $child['category_id'])	
				);

//$product_total = $this->model_catalog_product->getTotalProducts($data);

//$total += $product_total;

$total = 0;

$product_total = 0;

$children_data[] = array(

'category_id' => $child['category_id'],

//'name' => $child['name'] . ($this->config->get('config_product_count') ? ' (' . $product_total . ')' : ''),

'name' => $child['name'],

'href' => $this->url->link('product/category', 'path=' . $category['category_id'] . '_' . $child['category_id'])

);

Similar like before, we comment what we don’t want and initializing the variables with what we need. Now in this file you will also need to do it for the main categories, so far, you’ve only been doing it for subcategories. A bit below the code, you will find:

			$this->data['categories'][] = array(
				'category_id' => $category['category_id'],
				'name'        => $category['name'] . ($this->config->get('config_product_count') ? ' (' . $total . ')' : ''),
				'children'    => $children_data,
				'href'        => $this->url->link('product/category', 'path=' . $category['category_id'])
			);

$this->data['categories'][] = array(

'category_id' => $category['category_id'],

'name' => $category['name'] . ($this->config->get('config_product_count') ? ' (' . $total . ')' : ''),

'children' => $children_data,

'href' => $this->url->link('product/category', 'path=' . $category['category_id'])

);

Replace this with:

			$this->data['categories'][] = array(
				'category_id' => $category['category_id'],
				//'name'        => $category['name'] . ($this->config->get('config_product_count') ? ' (' . $total . ')' : ''),
				'name'        => $category['name'],
				'children'    => $children_data,
				'href'        => $this->url->link('product/category', 'path=' . $category['category_id'])
			);

$this->data['categories'][] = array(

'category_id' => $category['category_id'],

//'name' => $category['name'] . ($this->config->get('config_product_count') ? ' (' . $total . ')' : ''),

'name' => $category['name'],

'children' => $children_data,

'href' => $this->url->link('product/category', 'path=' . $category['category_id'])

);

Lastly, we have the file /catalog/controller/product/category.php, this is important for when user clicks on a main category with other subcategories.

Find the lines:

				$product_total = $this->model_catalog_product->getTotalProducts($data);				

				$this->data['categories'][] = array(
					'name'  => $result['name'] . ($this->config->get('config_product_count') ? ' (' . $product_total . ')' : ''),
					'href'  => $this->url->link('product/category', 'path=' . $this->request->get['path'] . '_' . $result['category_id'] . $url)
				);

$product_total = $this->model_catalog_product->getTotalProducts($data);

$this->data['categories'][] = array(

'name' => $result['name'] . ($this->config->get('config_product_count') ? ' (' . $product_total . ')' : ''),

'href' => $this->url->link('product/category', 'path=' . $this->request->get['path'] . '_' . $result['category_id'] . $url)

);

and replace them with:

				//$product_total = $this->model_catalog_product->getTotalProducts($data);				
				$product_total = 0;
				$this->data['categories'][] = array(
					//'name'  => $result['name'] . ($this->config->get('config_product_count') ? ' (' . $product_total . ')' : ''),
					'name'  => $result['name'],
					'href'  => $this->url->link('product/category', 'path=' . $this->request->get['path'] . '_' . $result['category_id'] . $url)
				);

//$product_total = $this->model_catalog_product->getTotalProducts($data);

$product_total = 0;

$this->data['categories'][] = array(

//'name' => $result['name'] . ($this->config->get('config_product_count') ? ' (' . $product_total . ')' : ''),

'name' => $result['name'],

'href' => $this->url->link('product/category', 'path=' . $this->request->get['path'] . '_' . $result['category_id'] . $url)

);

And that would be all that would slow down your website, you will have similar code lines in the file /catalog/controller/module/filter.php, but this is only called up one applying filters and I believe its best to leave it since you do want to show this information to your users. But for those who still want to look more on this, you should know its there.

That was all for this article, enjoy your new shopping script, it should be flying now :).

Will see you again in our next tutorial.