Author Topic: Where does this query come from?  (Read 3335 times)

AH

  • Global Moderator
  • Sr. Member
  • *
  • Posts: 2943
  • VirtueMart Version: 3.6.0
Re: Where does this query come from?
« Reply #15 on: November 26, 2018, 10:41:19 am »
you should be able to re-write the request in .htaccess

No need for a core change
regards
A

Joomla 3.9.8
php 7.1

themhz

  • Beginner
  • *
  • Posts: 13
Re: Where does this query come from?
« Reply #16 on: December 18, 2018, 02:07:47 am »
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

  • Contributing Developer
  • Sr. Member
  • *
  • Posts: 3978
  • Joomla & Virtuemart developper
    • Studio 42 - Virtuemart & Joomla extentions
  • VirtueMart Version: 2.6 & 3
Re: Where does this query come from?
« Reply #17 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.

themhz

  • Beginner
  • *
  • Posts: 13
Re: Where does this query come from?
« Reply #18 on: December 18, 2018, 13:56:54 pm »
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

  • Contributing Developer
  • Sr. Member
  • *
  • Posts: 3978
  • Joomla & Virtuemart developper
    • Studio 42 - Virtuemart & Joomla extentions
  • VirtueMart Version: 2.6 & 3
Re: Where does this query come from?
« Reply #19 on: December 18, 2018, 14:32:57 pm »
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

  • Beginner
  • *
  • Posts: 13
Re: Where does this query come from?
« Reply #20 on: December 18, 2018, 15:25:35 pm »
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

  • Contributing Developer
  • Sr. Member
  • *
  • Posts: 3978
  • Joomla & Virtuemart developper
    • Studio 42 - Virtuemart & Joomla extentions
  • VirtueMart Version: 2.6 & 3
Re: Where does this query come from?
« Reply #21 on: December 18, 2018, 15:35:00 pm »
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

  • Beginner
  • *
  • Posts: 13
Re: Where does this query come from?
« Reply #22 on: December 18, 2018, 15:56:52 pm »
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

  • Contributing Developer
  • Sr. Member
  • *
  • Posts: 3978
  • Joomla & Virtuemart developper
    • Studio 42 - Virtuemart & Joomla extentions
  • VirtueMart Version: 2.6 & 3
Re: Where does this query come from?
« Reply #23 on: December 18, 2018, 16:34:29 pm »
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).