1.5.0.5 very slow

[Xsecrets]

The product count is ok, but the menu doesn't add an extra sub item.
I didn't understand you at first, thanks for pointing me to this problem, ill see if I can think of something otherwise, too bad

EDIT:
Oc doesnt see the level-3 category as a subcategory of the level-2. Thats the problem.

actually as far as product counts it does, however no it does not show them in either the header list or the category module. In 1.5 the category list is not fully recursive it is only two levels deep, but that is a completely separate issue from the product counts.

[DirkZz]

Not exactly, if it was recursive then i could just count the products with an extra variable in the loop.

[Xsecrets]

I assure you it is recursive. Follow the programming all the way back you'll see it. It uses a recursive function to walk down the category tree and get the category id's for the entire tree starting with the requested category id.

[DirkZz]

Currenctly working on it .. ill show you what i ment.. almost done.. prob after the weekend.

[Xsecrets]

:P Currenctly working on it .. ill show you what i ment.. almost done.. prob after the weekend.

good luck I banged on it for about a week and couldn't figure out a way to do it with group by.

[DirkZz]

Its really hard I have to admit.. started working on the function today.
currently i have this:


Not quite ok yet.

[Xsecrets]

yeah I'm 99.999% sure it cannot be done with group by alone. You will have to have some sort of looping or something. I just don't believe it's possible to get it in one query.

[DirkZz]

yeah I'm 99.999% sure it cannot be done with group by alone. You will have to have some sort of looping or something. I just don't believe it's possible to get it in one query.

In 1 query is really almost impossible.
I will let PHP figure that out

[Xsecrets]

well best I could tell the next best step up from one query is what I released as my second vqmod earlier in this post.

[DirkZz]

Will certainly give it a try.

[DirkZz]

@Xsecrets .. im also breaking my balls on this ..
its so god darn hard lol, just like you said.

But there has to be something.. the must be an answer on the big world wide web.

[Xsecrets]

The best one I've come up with is the second vqmod I posted. It should work well. It does all the queries, but then caches the results, so the performance should be pretty good.

[sourcez]

Ok I've changed the way I approached it all. Here's a new vqmod. It basically does the same thing as the default install, but it rolls it all into the category model function and enables caching, so if someone wants to give this one a try I would love to hear if it helps. This one should not mess with the display at all.

This has made the script massively more useful for large carts - thank you for your efforts! Hopefully this will be in the core next time an update is made! (Or a better fix)

[drivenbyweb]

That mod helped tremendously. Great work!

[Moggin]

What's this
http://www.opencart.com/index.php?route ... me=danvaly

in extensions?

[Xsecrets]

What's this
http://www.opencart.com/index.php?route ... me=danvaly

in extensions?

looks like someone took my code from above and released it as a paid module. Well I don't know if they just copied the code or not, but I know the solution I have above for free does the same thing.

[dony_b]

Good call on the categories, Xsecrets.
Disabling categories on the homepage resulted in this....

Domain name Size Load Time Average Speed per KB
1 dev2.aspencreekoutdoors.com 10.9 KB 0.29 seconds 0.03 seconds

Similar results for product view...

Categories enabled
Domain name Size Load Time Average Speed per KB
1 dev2.aspencreekoutdoors.com/index.php?route=product/product&product_id=3795 83.18 KB 20.53 seconds 0.25 seconds

Categories disabled
Domain name Size Load Time Average Speed per KB
1 dev2.aspencreekoutdoors.com/index.php?route=product/product&product_id=3795 15.34 KB 1.85 seconds 0.12 seconds

ok well you or someone has obviously already modified your category module to remove the category counts.
edit catalog/controller/module/category.php and look for code something like this (you've probably already changed it a bit)

Code: Select all


        foreach ($categories as $category) {
            $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(
                    'category_id' => $child['category_id'],
                    'name'        => $child['name'] . ' (' . $product_total . ')',
                    'href'        => $this->url->link('product/category', 'path=' . $category['category_id'] . '_' . $child['category_id'])    
                );                    
            }
            
            $data = array(
                'filter_category_id'  => $category['category_id'],
                'filter_sub_category' => true    
            );        
                
            $product_total = $this->model_catalog_product->getTotalProducts($data);
                        
            $this->data['categories'][] = array(
                'category_id' => $category['category_id'],
                'name'        => $category['name'] . ' (' . $product_total . ')',
                'children'    => $children_data,
                'href'        => $this->url->link('product/category', 'path=' . $category['category_id'])
            );
        }

 

Change it to look like this

Code: Select all


        foreach ($categories as $category) {
            $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(
                    'category_id' => $child['category_id'],
                    'name'        => $child['name'],
                    'href'        => $this->url->link('product/category', 'path=' . $category['category_id'] . '_' . $child['category_id'])    
                );                    
            }
            
            $data = array(
                'filter_category_id'  => $category['category_id'],
                'filter_sub_category' => true    
            );        
                
            //$product_total = $this->model_catalog_product->getTotalProducts($data);
                        
            $this->data['categories'][] = array(
                'category_id' => $category['category_id'],
                'name'        => $category['name'],
                'children'    => $children_data,
                'href'        => $this->url->link('product/category', 'path=' . $category['category_id'])
            );
        }

 

the total next to the name is probably already removed, but make sure that you comment out the product total lines and see if that makes a difference in speed with the category module turned on.

This works fine on v 1.5.05 but I tried it on v1.5.1 and it doesn't work. Whats the difference from last version so I can apply it ? Thanks

[ccM]

This works fine on v 1.5.05 but I tried it on v1.5.1 and it doesn't work. Whats the difference from last version so I can apply it ? Thanks

Hi dony_b,

Did you find a solution to this problem? Would like to do the same thing with 1.5.1.

[fsit]

Applied the vmod fix of Xsecrets and went from 6~8 seconds first view and 4~6 seconds second view to a whopping 1st ~1.5s and seconf ~0,7! (http://www.webpagetest.org)
Now I am in business. Thanks!

I'm using 1.5.0.5

[moneycarlo]

Good call on the categories, Xsecrets.
Disabling categories on the homepage resulted in this....

Domain name Size Load Time Average Speed per KB
1 dev2.aspencreekoutdoors.com 10.9 KB 0.29 seconds 0.03 seconds

Similar results for product view...

Categories enabled
Domain name Size Load Time Average Speed per KB
1 dev2.aspencreekoutdoors.com/index.php?route=product/product&product_id=3795 83.18 KB 20.53 seconds 0.25 seconds

Categories disabled
Domain name Size Load Time Average Speed per KB
1 dev2.aspencreekoutdoors.com/index.php?route=product/product&product_id=3795 15.34 KB 1.85 seconds 0.12 seconds

ok well you or someone has obviously already modified your category module to remove the category counts.
edit catalog/controller/module/category.php and look for code something like this (you've probably already changed it a bit)

Code: Select all


        foreach ($categories as $category) {
            $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(
                    'category_id' => $child['category_id'],
                    'name'        => $child['name'] . ' (' . $product_total . ')',
                    'href'        => $this->url->link('product/category', 'path=' . $category['category_id'] . '_' . $child['category_id'])    
                );                    
            }
            
            $data = array(
                'filter_category_id'  => $category['category_id'],
                'filter_sub_category' => true    
            );        
                
            $product_total = $this->model_catalog_product->getTotalProducts($data);
                        
            $this->data['categories'][] = array(
                'category_id' => $category['category_id'],
                'name'        => $category['name'] . ' (' . $product_total . ')',
                'children'    => $children_data,
                'href'        => $this->url->link('product/category', 'path=' . $category['category_id'])
            );
        }

 

Change it to look like this

Code: Select all


        foreach ($categories as $category) {
            $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(
                    'category_id' => $child['category_id'],
                    'name'        => $child['name'],
                    'href'        => $this->url->link('product/category', 'path=' . $category['category_id'] . '_' . $child['category_id'])    
                );                    
            }
            
            $data = array(
                'filter_category_id'  => $category['category_id'],
                'filter_sub_category' => true    
            );        
                
            //$product_total = $this->model_catalog_product->getTotalProducts($data);
                        
            $this->data['categories'][] = array(
                'category_id' => $category['category_id'],
                'name'        => $category['name'],
                'children'    => $children_data,
                'href'        => $this->url->link('product/category', 'path=' . $category['category_id'])
            );
        }

 

the total next to the name is probably already removed, but make sure that you comment out the product total lines and see if that makes a difference in speed with the category module turned on.

This works fine on v 1.5.05 but I tried it on v1.5.1 and it doesn't work. Whats the difference from last version so I can apply it ? Thanks

This worked for me on 1.5.1.3. I didnt use the mod, just edited my category.php file and my counts are gone.
first time i only commented out the query and not the output ($product_total) and got errors. Once I edited out the output line it worked perfectly. It appears the vq mod does a few more updates but since my vqmod stuff dont work, i'll just stick with this.