Performance improvement suggestion for OPC Filter

If you are runnig Joomla 3.x and Virtuemart 3.x please post to this forum your questions or support tickets about One Page Checkout

Performance improvement suggestion for OPC Filter

Postby szablac » Mon Nov 03, 2025 10:52 am

Hi,

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!
szablac
 
Posts: 1
Joined: Mon Nov 03, 2025 10:44 am

Return to One Page Checkout for Virtuemart 3 on Joomla 3.x