Boost slow product list browse performance

[Udo]

I have set up a relatively small shop with 20 categories and about 4000 products using Joomla 1.5.15 and VirtueMart 1.1.4. I have added some products to multiple (parent) categories. The problem is that browsing the larger categories is very slow. Fetching a page with only 20 (of 900) products took more then 15 seconds, which seems way too long for such a small database.

I analyzed the $count and $list queries created by shop_browse_queries.php, and found the $count query runs 3 seconds, and the $list query runs 9 seconds. In $list, the query uses 9s to copy to tmp table. This is caused by the fact that no indexes are used when joining web_vm_product_category_xref, even though the indexes are available (product_category_xref_category_id,idx_product_category_xref_product_id)

I remember not using indexes is often caused by using (OR) in the where statement, as is the case in the $count & $list queries:

WHERE (`web_vm_product_category_xref`.`product_id`=`web_vm_product`.`product_id` OR `web_vm_product_category_xref`.`product_id`=`web_vm_product`.`product_parent_id`)

If the statement is replaced with:

WHERE `web_vm_product_category_xref`.`product_id`=`web_vm_product`.`product_id`

After that the indexes are used normally, and the query only needs 0.09 seconds !!!! to run in stead of 10s (more then 100x performance boost). As I am currently not using parent products, it's no problem to remove the second part.

I also noticed the query contains a useless where clause, and either the first or the second should be removed:


AND        `product_parent_id`=0
AND        (
          (`product_parent_id`='0')
        OR    (`product_parent_id`='')
        )

I'm not sure if this is a common issue or that I am the only one experiencing this and somehow my database is not performing as it should (if so I'd be interested in suggestions).

[szbstvn]

Hi!
I have a same problem, but this idea is nice for me.

[szbstvn]

Working! Great!

Tank You

[yenommer]

You are my hero!  I have been fighting with this same problem for sooo long. Thanks again man it really works great!

[Wallboy]

I have the same problem with the slow browse pages even when theres no products. With DEBUG enabled in VM, a 20 product browse page loads in roughly 11 seconds. If I take that browse page down in half, then it takes 5 seconds. I tried to modify the lines you mentioned, but am unsure which lines these are on. I found 2 lines that were similar.

[enoon]

Thanks it's a good speed gain. I also had a similar solution, but did now know where to search for the code to modify.
There is an alternative solution for the people who use product parents add:

   $where_clause[] = "`#__{vm}_product`.`product_id`=`#__{vm}_product_category_xref`.`product_id`";

before (line 310 in shop_browse_queries):   
   $where_clause[] = "((`#__{vm}_product`.`product_id`=`#__{vm}_product_price`.`product_id` AND `#__{vm}_shopper_group`.`shopper_group_id`=`#__{vm}_product_price`.`shopper_group_id`) OR `#__{vm}_product_price`.`product_id` IS NULL) ";

I must mentions that the speed gain is the same for both solutions. The issue was that the product was not linked to product_category_xref directly.

[cworthing]

I added the above code before the code you had said enoon. No difference.  Am I suppose to comment that longer piece of code out?

And my Line 310 is not that piece of code.
This is what my 310 is.

310.   // when someone is searching, we also show child products (product_parent_id != 0), but that's not the case here
   $where_clause[] = "`product_parent_id`=0 ";
}

The lines you refer to is line 306(I've never changed this file).

Is there a step im missing?  My site categories are the problem, clicking them frm the main page the pages come up sooo slowwwww. Been trying to fix this for a couple weeks now.

[djlongy]

I have set up a relatively small shop with 20 categories and about 4000 products using Joomla 1.5.15 and VirtueMart 1.1.4. I have added some products to multiple (parent) categories. The problem is that browsing the larger categories is very slow. Fetching a page with only 20 (of 900) products took more then 15 seconds, which seems way too long for such a small database.

I analyzed the $count and $list queries created by shop_browse_queries.php, and found the $count query runs 3 seconds, and the $list query runs 9 seconds. In $list, the query uses 9s to copy to tmp table. This is caused by the fact that no indexes are used when joining web_vm_product_category_xref, even though the indexes are available (product_category_xref_category_id,idx_product_category_xref_product_id)

I remember not using indexes is often caused by using (OR) in the where statement, as is the case in the $count & $list queries:

WHERE (`web_vm_product_category_xref`.`product_id`=`web_vm_product`.`product_id` OR `web_vm_product_category_xref`.`product_id`=`web_vm_product`.`product_parent_id`)

If the statement is replaced with:

WHERE `web_vm_product_category_xref`.`product_id`=`web_vm_product`.`product_id`

After that the indexes are used normally, and the query only needs 0.09 seconds !!!! to run in stead of 10s (more then 100x performance boost). As I am currently not using parent products, it's no problem to remove the second part.

I also noticed the query contains a useless where clause, and either the first or the second should be removed:


AND        `product_parent_id`=0
AND        (
          (`product_parent_id`='0')
        OR    (`product_parent_id`='')
        )

I'm not sure if this is a common issue or that I am the only one experiencing this and somehow my database is not performing as it should (if so I'd be interested in suggestions).

This is A LIFE SAVER. I have around 50000 products. I wanted them in the same group because there was no need for browsing, just searching products using SKU. But when user accidently selected that group it killed the site. So I've split this category to 40 subcategories to speed it up but it still takes ages to open one of these subcats.

Your solution changed the site to superfast!

Unfortunately VM does NOT handle large (well, if 50000 recs is large today) databases well. (

[MikeUK]

Unfortunately VM does NOT handle large (well, if 50000 recs is large today) databases well. (

I disagree, and don't think this is necessarily a fair comment. This is not just about VM. More important is the system admin and the server, as well as the setup / development of the site (especially what modules are used, modifications, etc).

A smooth system with this many products, with any e-commerce system, is down to a good / experienced system admin who knows what to do / what not to do, and can make the required modifications to cater for such a large number of products (which is the great thing about open-source software.

[djlongy]

Unfortunately VM does NOT handle large (well, if 50000 recs is large today) databases well. (

I disagree, and don't think this is necessarily a fair comment. This is not just about VM. More important is the system admin and the server, as well as the setup / development of the site (especially what modules are used, modifications, etc).

A smooth system with this many products, with any e-commerce system, is down to a good / experienced system admin who knows what to do / what not to do, and can make the required modifications to cater for such a large number of products (which is the great thing about open-source software.

I disagree on that

We run a dedicated linux (slackware) server on core2duo proc with 4gb ram etc. There are 5 virtual hosts (httpd), only one has this "big" database of products. After implementing the above tweak the site became superfast. Before that queries hogged the 100% cpu for minutes, not seconds.
All other vhosts run just perfect.

I agree there's always some room for additional optimizing but when mysql query runs as described, I cannot say other but that VM is not optimized for 50000+ products.

[MikeUK]

Fair enough, and good to see you have some decent equipment, but my point was that VM is open source, designed to be adjusted, and 50000+ products is not normal. So it is the responsibility of the website admin / developer, not VM.

I'm not saying VM cannot be improved, I'm saying it is not the fault of VM.

[cworthing]

Where is the place I would apply this change?

WHERE (`web_vm_product_category_xref`.`product_id`=`web_vm_product`.`product_id` OR `web_vm_product_category_xref`.`product_id`=`web_vm_product`.`product_parent_id`)

If the statement is replaced with:

WHERE `web_vm_product_category_xref`.`product_id`=`web_vm_product`.`product_id`

[djlongy]

Hi, find the modified file attached...

@Mike:

I agree that since VM is an open source there are some issues and that they can be solved by skilled people. However, I don't see why 50000+ product should not be normal? We have this many SPARE PARTS in the catalogue so being in the motorcycle business this is rather small amount of products in fact...

As for who's "fault" it is for such poor performace...programmer's of course. I'm not trying to be offensive, I just want to say that regarding my problem so far I got only these (some contradictory) statements:
- 50000 is too much, reduce number of products (I can't, remember?)
- 50000 is no problem for VM, it runs very smoothly, YOU have a problem (with server, setup etc)
- not logical why you have 50000 in the same group (it is logical when people use only search by product code to add to cart, they are not intended to browse...)
-etc.
So I still claim that VM as it is, no tweaks, is not doing well with 50000 products (or 25000 for that mater).
Which, since it's open source, is not something catastrophic but it's nice that it's admitted and some good coders are able to fix it instead we all go finding problems on servers etc.

Therefore this current tweak is a blessing in my eyes...but VM is still an awesome product, for me the only choice really.



[attachment cleanup by admin]

[cworthing]

djlongy, is that file intended for me? If it was, I replaced that file with mine(backed mine up) but it did no speed-ups or slowdowns for the category browsing or search function.

[djlongy]

djlongy, is that file intended for me? If it was, I replaced that file with mine(backed mine up) but it did no speed-ups or slowdowns for the category browsing or search function.

Yeah, this was for you. In my case it caused an overall speed improvement it's hard to imagine. Any category opens in a second, especially the 1000+ product categories that took more than a minute to open now show the product list in two seconds.

If it doesn't speed up your site then perhaps you have a problem elsewhere :?