Database overload in products inventory

[role74]

Hi there

I've got a notice from my webhoster that the server gets overloaded in certain moments when a database query is send from my site. As far that I figured out, it happens when I go to the products inventory and choose to show products with low inventory. This happens since upgrading to VM 3.4.2.
See also the attached query that causes the problem.

Any clues how to solve this? Don't want to get my site shut down by the hoster...

I'm using virtuemart 3.4.2 with Joomla 3.8.13.
Database MySQLi 5.5.5-10.1.35-MariaDB and PHP 7.2.11

any help is welcome.

cheers from switzerland
role

[Studio 42]

In your query.
Why all this products id ?
ORDER BY RAND() ?
I dont checked the code, but the query is really strange in the back-end

[caesarsk]

Yep. product.php (administrator/models)...
With this problem I have encountered too, but since I did modify this file, I was not paying attention at this time.

This is what happens to me if I use module VM_products and set a product order according to the best seller and show 10 products (cetegory id 0, products in shop 20k+). if the display of fewer products (eg. 2) is set, the error will not occur.

[role74]

...it's all standard VM, no modifications...

[GJC Web Design]

you must have some other plugin etc ..

administrator/index.php?option=com_virtuemart&view=inventory

the query for that view - low inventory is

SELECT SQL_CALC_FOUND_ROWS  p.`virtuemart_product_id`
      
  FROM `4k9_virtuemart_products` as p   

  LEFT JOIN `4k9_virtuemart_product_categories` as pc
  ON p.`virtuemart_product_id` = `pc`.`virtuemart_product_id`

  WHERE (p.`product_in_stock`- p.`product_ordered` < p.`low_stock_notification`)

  group by p.`virtuemart_product_id`

  ORDER BY `pc`.`ordering` DESC, p.`virtuemart_product_id` DESC
  LIMIT 0, 30

and then some more for media and details etc

that query you post makes no sense in the admin .. why would any admin task require a RAND display? and why all the NOT = ?

[role74]

Hi all

I've got it...it was just bad luck it happened when checking the product inventory.

Some time ago I changed the home main menu entry from the old standard layout to the new virtuemart category layout menu and disabled the legacy mode in the shop configuration.
This was the point where all began.

Don't know if it's caused by the template or the category view layout but it stopped when I switched back.

Any clues where to look in order to get it solved?

cheers
-role

[GJC Web Design]

compare your template files with the standard ones -- from memory it is only category template stuff that has legacy etc