News:

You may pay someone to create your store, or you visit our seminar and become a professional yourself with the silver certification

Main Menu

Where does this query come from?

Started by behemoth, November 13, 2018, 20:48:29 PM

Previous topic - Next topic

AH

you should be able to re-write the request in .htaccess

No need for a core change
Regards
A

Joomla 3.10.11
php 8.0

themhz

I am having the same problem. This is what the log shows from my cpanel cpu usage.. and more like these. CPU is like 100%. I have also changed all the tables to innoDB but it doesn't make anything better. Can anyone help please? What is this long query monster doing?

SELECT SQL_CALC_FOUND_ROWS  p.`virtuemart_product_id`
      FROM `kipodo_virtuemart_products` as p   
LEFT JOIN `kipodo_virtuemart_product_shoppergroups` as ps ON p.`virtuemart_product_id` = `ps`.`virtuemart_product_id` 
LEFT JOIN `kipodo_virtuemart_product_categories` as pc ON p.`virtuemart_product_id` = `pc`.`virtuemart_product_id`
WHERE ( ((p.`product_parent_id` = "0" AND `pc`.`virtuemart_category_id` > "0") OR p.`product_parent_id` > "0")  AND  ((p.`product_parent_id` > "0" AND `pc`.`virtuemart_category_id` > "0") OR p.`product_parent_id` = "0")  AND  ( `ps`.`virtuemart_shoppergroup_id`= "1"  OR `ps`.`virtuemart_shoppergroup_id` IS NULL  )  AND  p.`published`="1"  AND  p.`virtuemart_product_id`!=48 AND p.`virtuemart_product_id`!=182 AND p.`virtuemart_product_id`!=184 AND p.`virtuemart_product_id`!=407 AND p.`virtuemart_product_id`!=436 AND p.`virtuemart_product_id`!=437 AND p.`virtuemart_product_id`!=557 AND p.`virtuemart_product_id`!=558 AND p.`virtuemart_product_id`!=564 AND p.`virtuemart_product_id`!=639 AND p.`virtuemart_product_id`!=711 AND p.`virtuemart_product_id`!=739 AND p.`virtuemart_product_id`!=762 AND p.`virtuemart_product_id`!=770 AND p.`virtuemart_product_id`!=889 AND p.`virtuemart_product_id`!=937 AND p.`virtuemart_product_id`!=1141 AND p.`virtuemart_product_id`!=1142 AND p.`virtuemart_product_id`!=1212 AND p.`virtuemart_product_id`!=1213 AND p.`virtuemart_product_id`!=1214 AND p.`virtuemart_product_id`!=1215 AND p.`virtuemart_product_id`!=1216 AND p.`virtuemart_product_id`!=1217 AND p.`virtuemart_product_id`!=1218 AND p.`virtuemart_product_id`!=1219 AND p.`virtuemart_product_id`!=1220 AND p.`virtuemart_product_id`!=1221 AND p.`virtuemart_product_id`!=1222 AND p.`virtuemart_product_id`!=1223 AND p.`virtuemart_product_id`!=1224 AND p.`virtuemart_product_id`!=1225 AND p.`virtuemart_product_id`!=1226 AND p.`virtuemart_product_id`!=1227 AND p.`virtuemart_product_id`!=1228 AND p.`virtuemart_product_id`!=1229 AND p.`virtuemart_product_id`!=1248 AND p.`virtuemart_product_id`!=1273 AND p.`virtuemart_product_id`!=1274 AND p.`virtuemart_product_id`!=1275 AND p.`virtuemart_product_id`!=1289 AND p.`virtuemart_product_id`!=1293 AND p.`virtuemart_product_id`!=1296 AND p.`virtuemart_product_id`!=1297 AND p.`virtuemart_product_id`!=1299 AND p.`virtuemart_product_id`!=1301 AND p.`virtuemart_product_id`!=1304 AND p.`virtuemart_product_id`!=1305 AND ... AND MORE LIKE THESE not equal things. I cant post them because its the post cant accept more the 20000 charachters.. )
group by p.`virtuemart_product_id`
ORDER BY `pc`.`ordering` ASC, p.`virtuemart_product_id` ASC LIMIT 0, 9"

Studio 42

This is when you active to not have same result in the featured, last products ... in the config.

themhz

Quote from: Studio 42 on December 18, 2018, 02:35:34 AM
This is when you active to not have same result in the featured, last products ... in the config.

How do I change that?

Studio 42

In Virtuemart config>Templates setting you have a column "Omit" for each type.
Set it to No, do not add to query all products id.

themhz

Ok I check No to every omit (Show featured, Show discontinued, Show Top ten products, Show recent, Show latest products) However only Show featured is Show = Yes. I killed the processes and
scanned the site with https://spamcheckpro and I still get high cpu loads and the site doesn't work again because I am still getting many queries of the same type.

Studio 42

The query is not because displaying or not, but using the parameter "Omit" Yes.
This add each IDs to the query if it set to Yes.
try to change the pagination to only display 24 products or less.
If you have many children, the problem can be because the children are loaded too.
Because i dont know exactly your product/child/category tree, how you use customfields ... it's hard to find the guilty.

themhz

I have 150 categories 1166 products and I do have child products because the user needs to select a type of a specific product. The only way to work this around is with a sub product. Is there a script to run so I can show the current status of my virtuemart similar to Forum Post Assistan -FPA ??

Studio 42

But does the query persist?
If not, then you should verify other queries using Joomla debug to show slow queries(at bottom in most template).