First of all, thanks for the great work on the OPC extension — it's been very useful for me and my clients.
While testing the category filter on shipping and payment methods, I noticed a significant performance issue related to one of the SQL queries.
When using OPC category filters in environments with a large number of products (10,000+), the cart page can become extremely slow to load. After some investigation, it looks like this may be caused by the SQL statement on line 155 of com_onepage/helpers/filters.php.
Current query:
- Code: Select all
$q = 'select c.`virtuemart_product_id` from #__virtuemart_product_categories as c, #__virtuemart_products as p where c.virtuemart_category_id IN ('.implode(',', $ids).') and (c.virtuemart_product_id IN ('.implode(',', $pids).')) or (p.product_parent_id = c.virtuemart_product_id and p.virtuemart_product_id IN ('.implode(',', $pids).') ) limit 0,1';
Suggested improvement:
- Code: Select all
$q = 'select c.`virtuemart_product_id` from #__virtuemart_product_categories as c, #__virtuemart_products as p where c.virtuemart_category_id IN ('.implode(',', $ids).') and (c.virtuemart_product_id IN ('.implode(',', $pids).') and c.virtuemart_product_id=p.virtuemart_product_id) or (p.product_parent_id = c.virtuemart_product_id and p.virtuemart_product_id IN ('.implode(',', $pids).') ) limit 0,1';
It seems that without the additional join condition, the query returns the full product table, which leads to unnecessary processing.
The updated condition ensures only products that are in the cart are evaluated, and dramatically improves page load time in our tests.
J5.4.0
VM4.6.0
OPC2.0.457
Could you take a look when you have a moment?
Happy to provide additional details or help test further if needed.
Thanks again for your great work!
