Fix opencart loading very slow

Must Read : 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'

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'])
 );

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'])
 );

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']) 
 );

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']) 
 );

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 . ')' : ''),
 'name'        => $category['name'],
 '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'])
 );

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)
 );

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)
 );

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.

 

Convert A Date Into Timestamp In JavaScript

Must Read : Convert A Date Into Timestamp In JavaScript

To get the unix timestamp using JavaScript you need to use the getTime() function of the build in Date object. As this returns the number of milliseconds then we must divide the number by 1000 and round it in order to get the timestamp in seconds.

Math.round(new Date().getTime()/1000);

To convert a date into a timestamp we need to use the UTC() function of the Date object. This function takes 3 required parameters and 4 optional parameters. The 3 required parameters are the year, month and day, in that order. The optional 4 parameters are the hours, minutes, seconds and milliseconds of the time, in that order.

To create the time do something like this:

var datum = new Date(Date.UTC('2009','01','13','23','31','30'));
return datum.getTime()/1000;

This prints out 1234567890. Here is a function to simplify things:

function toTimestamp(year,month,day,hour,minute,second){
 var datum = new Date(Date.UTC(year,month-1,day,hour,minute,second));
 return datum.getTime()/1000;
}

Notice that when adding in the month you need to minus the value by 1. This is because the function requires a month value between 0 and 11. However, there is an easier way of getting the timestamp by using the parse() function. This function returns the timestamp in milliseconds, so we need to divide it by 1000 in order to get the timestamp.

function toTimestamp(strDate){
 var datum = Date.parse(strDate);
 return datum/1000;
}

This can be run by using the following, not that the date must be month/day when writing the date like this.

 alert(toTimestamp('02/13/2009 23:31:30'));