How to improve getTotalProducts on Opencart controllers

Question

i didnt know before but there is a huge bug in Opencart official release, in a store when you have a lot, of products & categories, it takes upon 50sec!!! to load a page. 50sec!!! , i take a look into the code and google, and found that the problem is well documented, as almost everybody know that this line is causing everything. (counting from cache)

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

The solution posted every where consists in comment out this line , wich by the way at least for me works better if i just set $product_total to be empty.. Like this

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

                $product_total = "";

Anyway my problem was solved (the page load in 3 seconds instead of 50 sec) but the count was missing so i keep looking and finally i found this solution wich is pretty much , (until now, still testing) the best solution if your database is handling with a lot of products and categories..http://ergopho.be/speeding-up-opencart-using-the-cache-class/

What it does is basically wrap this entire section of code in an if block, and first checking if the file exists in the cache. If it does not, we run it like normal, and then store it to the cache. If it does, use the cached version instead.

In Controller/Common/Header.php you can found also this code (in the article he do it on categories) here is the code in this file also

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

$categories = $this->model_catalog_category->getCategories(0);



        foreach ($categories as $category) {

            if ($category['top']) {

                // Level 2

                $children_data = array();



                $children = $this->model_catalog_category->getCategories($category['category_id']);



                foreach ($children as $child) {

                    $data = array(

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

                        'filter_sub_category' => true

                    );



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

                    );                      

                }


Yo have to wrap all this code into this lines

$this->data['categories'] = $this->cache->get('categories');
if(!count($this->data['categories'])) { 

<!--Here goes the above code-->

$this->cache->set('categories', $this->data['categories']);
}

Its working fine so far, i hope this help some one else, also pls note that if you have a better way of doing this, i know there is a lot of not advanced users looking for this, so if you can share it with us, it would be great.

¿Can anyone figure out a better fix for this awful slowy bug?

Thanks and lest hope for the next version of Opencart this kind of issues are fixed.

Hope this helps. Peace

Answer 1

This really should go into the model as opposed to the controller.

I have a site that has over 15,000 active products. My method for this looks like so:

public function getTotalProducts($data = array()) {
    if ($this->customer->isLogged()):
        $customer_group_id = $this->customer->getCustomerGroupId();
    else:
        $customer_group_id = $this->config->get('config_customer_group_id');
    endif;  

    $sql = "SELECT COUNT(DISTINCT p.product_id) AS total"; 

    if (!empty($data['filter_category_id'])):
        if (!empty($data['filter_sub_category'])):
            $sql .= " FROM {$this->prefix}category_path cp 
                      LEFT JOIN {$this->prefix}product_to_category p2c 
                        ON (cp.category_id = p2c.category_id)";         
        else:
            $sql .= " FROM {$this->prefix}product_to_category p2c";
        endif;

        if (!empty($data['filter_filter'])):
            $sql .= " LEFT JOIN {$this->prefix}product_filter pf 
                        ON (p2c.product_id = pf.product_id) 
                      LEFT JOIN {$this->prefix}product p 
                        ON (pf.product_id = p.product_id)";
        else:
            $sql .= " LEFT JOIN {$this->prefix}product p 
                        ON (p2c.product_id = p.product_id)";
        endif;
    else:
        $sql .= " FROM {$this->prefix}product p";
    endif;

    $sql .= " LEFT JOIN {$this->prefix}product_description pd 
                ON (p.product_id = pd.product_id) 
              LEFT JOIN {$this->prefix}product_to_store p2s 
                ON (p.product_id = p2s.product_id) 
              WHERE pd.language_id = '" . (int)$this->config->get('config_language_id') . "' 
              AND p.status = '1' 
              AND p.date_available <= NOW() 
              AND p2s.store_id = '" . (int)$this->config->get('config_store_id') . "'";

    if (!empty($data['filter_category_id'])):
        if (!empty($data['filter_sub_category'])):
            $sql .= " AND cp.path_id = '" . (int)$data['filter_category_id'] . "'"; 
        else:
            $sql .= " AND p2c.category_id = '" . (int)$data['filter_category_id'] . "'";            
        endif;  

        if (!empty($data['filter_filter'])):
            $implode = array();

            $filters = explode(',', $data['filter_filter']);

            foreach ($filters as $filter_id):
                $implode[] = (int)$filter_id;
            endforeach;

            $sql .= " AND pf.filter_id IN (" . implode(',', $implode) . ")";                
        endif;
    endif;

    if (!empty($data['filter_name']) || !empty($data['filter_tag'])):
        $sql .= " AND (";

        if (!empty($data['filter_name'])):
            $implode = array();

            $words = explode(' ', trim(preg_replace('/\s\s+/', ' ', $data['filter_name'])));

            foreach ($words as $word):
                $implode[] = "pd.name LIKE '%" . $this->db->escape($word) . "%'";
            endforeach;

            if ($implode):
                $sql .= " " . implode(" AND ", $implode) . "";
            endif;

            if (!empty($data['filter_description'])):
                $sql .= " OR pd.description LIKE '%" . $this->db->escape($data['filter_name']) . "%'";
            endif;
        endif;

        if (!empty($data['filter_name']) && !empty($data['filter_tag'])):
            $sql .= " OR ";
        endif;

        if (!empty($data['filter_tag'])):
            $sql .= "pd.tag LIKE '%" . $this->db->escape(utf8_strtolower($data['filter_tag'])) . "%'";
        endif;

        if (!empty($data['filter_name'])):
            $sql .= " OR LCASE(p.model) = '" . $this->db->escape(utf8_strtolower($data['filter_name'])) . "'";
        endif;

        if (!empty($data['filter_name'])):
            $sql .= " OR LCASE(p.sku) = '" . $this->db->escape(utf8_strtolower($data['filter_name'])) . "'";
        endif;

        if (!empty($data['filter_name'])):
            $sql .= " OR LCASE(p.upc) = '" . $this->db->escape(utf8_strtolower($data['filter_name'])) . "'";
        endif;

        if (!empty($data['filter_name'])):
            $sql .= " OR LCASE(p.ean) = '" . $this->db->escape(utf8_strtolower($data['filter_name'])) . "'";
        endif;

        if (!empty($data['filter_name'])):
            $sql .= " OR LCASE(p.jan) = '" . $this->db->escape(utf8_strtolower($data['filter_name'])) . "'";
        endif;

        if (!empty($data['filter_name'])):
            $sql .= " OR LCASE(p.isbn) = '" . $this->db->escape(utf8_strtolower($data['filter_name'])) . "'";
        endif;

        if (!empty($data['filter_name'])):
            $sql .= " OR LCASE(p.mpn) = '" . $this->db->escape(utf8_strtolower($data['filter_name'])) . "'";
        endif;

        $sql .= ")";                
    endif;

    if (!empty($data['filter_manufacturer_id'])):
        $sql .= " AND p.manufacturer_id = '" . (int)$data['filter_manufacturer_id'] . "'";
    endif;

    $cache = md5 ((int)$customer_group_id . serialize ($data));

    $total = $this->cache->get('product.total.products.' . $cache);

    if (!$total):

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

        $this->cache->set('product.total.products.' . $cache, $total);

    endif;

    return $total;
}

You'll notice the caching section near the end there.

I also converted over to memcached for caching query results as opposed to file cache. The real problem comes in when you use seo_urls for this many products, I had to completely rewite the entire SeoUrl Controller and the Url library, but it's fast and I've got awesome urls :)