VirtueMart Forum

VirtueMart 2 + 3 + 4 => Security (https) / Performance / SEO, SEF, URLs => Topic started by: behemoth on November 13, 2018, 20:48:29 PM

Title: Where does this query come from?
Post by: behemoth on November 13, 2018, 20:48:29 PM
The following query is running intermittently, often multiple times concurrently, even when the site is in offline mode.

SELECT SQL_CALC_FOUND_ROWS  p.`virtuemart_product_id`, l.product_name               
FROM `xxxxx_virtuemart_products` as p LEFT JOIN `xxxxx_virtuemart_products_en_gb` as l
ON l.`virtuemart_product_id` = p.`virtuemart_product_id` LEFT JOIN `xxxxx_virtuemart_product_shoppergroups` as ps
ON p.`virtuemart_product_id` = `ps`.`virtuemart_product_id` LEFT JOIN `xxxxx_virtuemart_product_categories` as pc
ON p.`virtuemart_product_id` = `pc`.`virtuemart_product_id`
WHERE ( ( `ps`.`virtuemart_shoppergroup_id`= "1"  OR `ps`.`virtuemart_shoppergroup_id` IS NULL  ) 
AND  p.`published`="1" ) group by p.`virtuemart_product_id` ORDER BY `pc`.`ordering` ASC, `product_name` ASC LIMIT 0, 46

Because we have a lot of products, it takes a while to run and from time to time impacts the CPU usage of the server.

I know that the limit (46) is controlled by "Frontend default items per list view" within "Set the pagination sequence for the List Box" in the config.

I did wonder if it was related to the reported 404 issue, but I tried disabling 404 handling and this is still occurring.
Title: Re: Where does this query come from?
Post by: Studio 42 on November 13, 2018, 22:32:31 PM
SELECT SQL_CALC_FOUND_ROWS  p.`virtuemart_product_id`, l.product_name  is not from Virtuemart core modules or component.
Have you really set 46 as pagination limit ?
All query i know in Vm use
SELECT SQL_CALC_FOUND_ROWS  p.`virtuemart_product_id`
or
SELECT SQL_CALC_FOUND_ROWS ,p.*,l.* ....
Try to disable some third party modules and use Joomla debug to verify the queries.
Title: Re: Where does this query come from?
Post by: behemoth on November 14, 2018, 12:07:11 PM
Hi Studio 42, thanks for your reply.

Why is 46 a bad pagination limit? It was originally 48 (so I could lay out eight rows of six products), but I amended it to determine which of the config options was controlling it. I will be setting it back once this is sorted unless there's a good reason not to.

How do you know what queries are used? I'm trying to work my way through the code but I'm no expert.

The problem I'm having isn't specifically related to the site itself; sometimes this query fires multiple times and it eventually brings down the server. I'm currently trying to determine what's triggering it.
Title: Re: Where does this query come from?
Post by: behemoth on November 14, 2018, 16:33:31 PM
Ok, I've searched through files of all the addons that are installed on the site and none of them use "SELECT SQL_CALC_FOUND_ROWS" at all.

I did find this:
administrator\components\com_virtuemart\helpers\vmmodel.php:                    $q = 'SELECT SQL_CALC_FOUND_ROWS '.$select.$joinedTables;

It's within a function called exeSortSearchListQuery(), but I can't do anything that will trigger this query - I've tried browsing categories, searching, playing with the pagination, but I've not seen this query fire, yet there are times when we notice the CPU loads rising or the emails go down or whatever and this query is present in the processlist.

Any advice gratefully received.
Title: Re: Where does this query come from?
Post by: Studio 42 on November 14, 2018, 22:17:35 PM
Do you use last release or Vm2 ?
Have you try to update Virtuemart ?
I never use more then 24 or 36 because in some VM release the product Object can be very big. Especially if you display(or load) custom fields in Category view or have to much child products ...
The Query you send is not slow. You can check the slow mySql queries on activate debug in the Joomla configuration (on bottom of the template you have a "debug" zone with all speed and memory statistics.
Note that if you have already a slowdown problem, set pagination to 24 for eg the time you debug. Or you get a blank page because time limit or memory limit.
Title: Re: Where does this query come from?
Post by: behemoth on November 15, 2018, 11:16:57 AM
It's not the latest VM, no. It's such an unpredictable symptom that I need to determine what's causing this so I can tell if upgrading has actualy fixed it.

The query itself takes a long time to run because we have a lot of products in the database (about 1.6 million). It's not particulary impacting site usage, it's just the MySQL CPU load. I'm trying to run something else and this query keeps spawning and hogging all the resources. Yesterday evening I had three instances of the query running at 17:45 which took 424, 417 and 395 seconds to complete!

I've tried reducing the pagination in the hope that the query takes less time to run, but I still don't know what's making it run at all...
Title: Re: Where does this query come from?
Post by: Studio 42 on November 15, 2018, 11:48:17 AM
Have you do some test directly in mysql using PHP Myadmin for eg ?
Perhaps some table optimisation ?
Fine adjust the mysql settings ....
If you have 1.6 million products, you cannot simply use a shop solution and hope it work, your server cache, mysql settings need certainly some changes.
Perhaps you need more memory, use SSD ... the query itself is not bad
Title: Re: Where does this query come from?
Post by: behemoth on November 15, 2018, 12:43:17 PM
Hi,

I have added some additional indexing on the tables, and while not perfect the site is functioning reasonably. I just want to be able to replicate the situation where this query fires, especially when it fires multiple times. Is it a particular search? Viewing a particular category? Whenever I've taken steps to act in a way that I imagine should initiate the query, it doesn't appear in 'show processlist', but sometimes it fires and I don't know why. Is this other visitors? Is there something internally kicking it off? It's more noticeable when I'm performing my own import routine to get products into the database, but there isn't a direct correlation.
Title: Re: Where does this query come from?
Post by: Studio 42 on November 15, 2018, 13:02:21 PM
When you enable Joomla debug, you see the queries in the debug zone.
You have a pagination 46 as if this was a category view query, but no category is set. So perhaps the main shop(or category view root) if you display the products in this view
Title: Re: Where does this query come from?
Post by: Milbo on November 19, 2018, 13:57:27 PM
Did you enable the product_name for the search? looks for me like the search query.
Title: Re: Where does this query come from?
Post by: behemoth on November 22, 2018, 18:41:07 PM
product_name is enabled by default.

why would it need to run dozens of times a second?

I'm right in the middle of another spike in server load. I've tried disabling that. I'll report back later.
Title: Re: Where does this query come from?
Post by: Studio 42 on November 22, 2018, 18:50:29 PM
Or try to add an index for  : ordering + product_name, if you have not to long product name, the  ORDER BY `pc`.`ordering` ASC, `product_name` is certainly the slowdown because it use a full table scan
Using a combinated ordering + product_name permit to mysql to use an index only for this case.
Of course if you ave many other ordering, it can be slower to add index for each , so it depend really your own needs.
Title: Re: Where does this query come from?
Post by: behemoth on November 22, 2018, 19:12:46 PM
This is what's happening in MySQL at the time:

mysql> show processlist;
+--------+----------------+-----------+-------------------------+---------+------+---------------------------+------------------------------------------------------------------------------------------------------+
| Id     | User           | Host      | db                      | Command | Time | State                     | Info                                                                                                 |
+--------+----------------+-----------+-------------------------+---------+------+---------------------------+------------------------------------------------------------------------------------------------------+
| 530446 | azeeda_L505b60 | localhost | azeeda_AZ_live_20181120 | Query   |    0 | init                      | show processlist                                                                                     |
| 548483 | azeeda_L505b60 | localhost | azeeda_AZ_live_20181120 | Query   |  616 | Sending data              | SELECT SQL_CALC_FOUND_ROWS  p.`virtuemart_product_id`, l.product_name
                FROM `xxxxx_virtuemart_prod |
| 548489 | azeeda_L505b60 | localhost | azeeda_AZ_live_20181120 | Query   |  616 | Sending data              | SELECT SQL_CALC_FOUND_ROWS  p.`virtuemart_product_id`, l.product_name
                FROM `xxxxx_virtuemart_prod |
| 548956 | azeeda_L505b60 | localhost | azeeda_AZ_live_20181120 | Query   |  616 | Sending data              | SELECT SQL_CALC_FOUND_ROWS  p.`virtuemart_product_id`, l.product_name
                FROM `xxxxx_virtuemart_prod |
| 549036 | azeeda_L505b60 | localhost | azeeda_AZ_live_20181120 | Query   |  615 | Sending data              | SELECT SQL_CALC_FOUND_ROWS  p.`virtuemart_product_id`, l.product_name
                FROM `xxxxx_virtuemart_prod |
| 549037 | azeeda_L505b60 | localhost | azeeda_AZ_live_20181120 | Query   |  617 | Sending data              | SELECT SQL_CALC_FOUND_ROWS  p.`virtuemart_product_id`, l.product_name
                FROM `xxxxx_virtuemart_prod |
| 549038 | azeeda_L505b60 | localhost | azeeda_AZ_live_20181120 | Query   |  617 | Sending data              | SELECT SQL_CALC_FOUND_ROWS  p.`virtuemart_product_id`, l.product_name
                FROM `xxxxx_virtuemart_prod |
| 549224 | azeeda_L505b60 | localhost | azeeda_AZ_live_20181120 | Query   |  615 | Sending data              | SELECT SQL_CALC_FOUND_ROWS  p.`virtuemart_product_id`, l.product_name
                FROM `xxxxx_virtuemart_prod |
| 549267 | azeeda_L505b60 | localhost | azeeda_AZ_live_20181120 | Query   |  614 | Sending data              | SELECT SQL_CALC_FOUND_ROWS  p.`virtuemart_product_id`, l.product_name
                FROM `xxxxx_virtuemart_prod |
| 549364 | azeeda_L505b60 | localhost | azeeda_AZ_live_20181120 | Query   |  614 | Sending data              | SELECT SQL_CALC_FOUND_ROWS  p.`virtuemart_product_id`, l.product_name
                FROM `xxxxx_virtuemart_prod |
| 549365 | azeeda_L505b60 | localhost | azeeda_AZ_live_20181120 | Query   |  614 | Sending data              | SELECT SQL_CALC_FOUND_ROWS  p.`virtuemart_product_id`, l.product_name
                FROM `xxxxx_virtuemart_prod |
| 549368 | azeeda_L505b60 | localhost | azeeda_AZ_live_20181120 | Query   |  614 | Sending data              | SELECT SQL_CALC_FOUND_ROWS  p.`virtuemart_product_id`, l.product_name
                FROM `xxxxx_virtuemart_prod |
| 549401 | azeeda_L505b60 | localhost | azeeda_AZ_live_20181120 | Query   |  691 | Sending data              | SELECT SQL_CALC_FOUND_ROWS  p.`virtuemart_product_id`, l.product_name
                FROM `xxxxx_virtuemart_prod |
| 549402 | azeeda_L505b60 | localhost | azeeda_AZ_live_20181120 | Query   |  691 | Sending data              | SELECT SQL_CALC_FOUND_ROWS  p.`virtuemart_product_id`, l.product_name
                FROM `xxxxx_virtuemart_prod |
| 549403 | azeeda_L505b60 | localhost | azeeda_AZ_live_20181120 | Query   |  691 | Sending data              | SELECT SQL_CALC_FOUND_ROWS  p.`virtuemart_product_id`, l.product_name
                FROM `xxxxx_virtuemart_prod |
| 549404 | azeeda_L505b60 | localhost | azeeda_AZ_live_20181120 | Query   |  691 | Sending data              | SELECT SQL_CALC_FOUND_ROWS  p.`virtuemart_product_id`, l.product_name
                FROM `xxxxx_virtuemart_prod |
| 549405 | azeeda_L505b60 | localhost | azeeda_AZ_live_20181120 | Query   |  691 | Sending data              | SELECT SQL_CALC_FOUND_ROWS  p.`virtuemart_product_id`, l.product_name
                FROM `xxxxx_virtuemart_prod |
| 549406 | azeeda_L505b60 | localhost | azeeda_AZ_live_20181120 | Query   |  691 | Sending data              | SELECT SQL_CALC_FOUND_ROWS  p.`virtuemart_product_id`, l.product_name
                FROM `xxxxx_virtuemart_prod |
| 549407 | azeeda_L505b60 | localhost | azeeda_AZ_live_20181120 | Query   |  691 | Sending data              | SELECT SQL_CALC_FOUND_ROWS  p.`virtuemart_product_id`, l.product_name
                FROM `xxxxx_virtuemart_prod |
| 549408 | azeeda_L505b60 | localhost | azeeda_AZ_live_20181120 | Query   |  691 | Sending data              | SELECT SQL_CALC_FOUND_ROWS  p.`virtuemart_product_id`, l.product_name
                FROM `xxxxx_virtuemart_prod |
| 549409 | azeeda_L505b60 | localhost | azeeda_AZ_live_20181120 | Query   |  691 | Sending data              | SELECT SQL_CALC_FOUND_ROWS  p.`virtuemart_product_id`, l.product_name
                FROM `xxxxx_virtuemart_prod |
| 549410 | azeeda_L505b60 | localhost | azeeda_AZ_live_20181120 | Query   |  691 | Sending data              | SELECT SQL_CALC_FOUND_ROWS  p.`virtuemart_product_id`, l.product_name
                FROM `xxxxx_virtuemart_prod |
| 549411 | azeeda_L505b60 | localhost | azeeda_AZ_live_20181120 | Query   |  691 | Sending data              | SELECT SQL_CALC_FOUND_ROWS  p.`virtuemart_product_id`, l.product_name
                FROM `xxxxx_virtuemart_prod |
| 549412 | azeeda_L505b60 | localhost | azeeda_AZ_live_20181120 | Query   |  691 | Sending data              | SELECT SQL_CALC_FOUND_ROWS  p.`virtuemart_product_id`, l.product_name
                FROM `xxxxx_virtuemart_prod |
| 549413 | azeeda_L505b60 | localhost | azeeda_AZ_live_20181120 | Query   |  691 | Sending data              | SELECT SQL_CALC_FOUND_ROWS  p.`virtuemart_product_id`, l.product_name
                FROM `xxxxx_virtuemart_prod |

.
.
.
lines removed to fit within forum limit
.
.
.


| 554304 | azeeda_L505b60 | localhost | azeeda_AZ_live_20181120 | Query   |  108 | Sending data              | SELECT SQL_CALC_FOUND_ROWS  p.`virtuemart_product_id`, l.product_name
                FROM `xxxxx_virtuemart_prod |
| 554350 | azeeda_L505b60 | localhost | azeeda_AZ_live_20181120 | Query   |  103 | Sending data              | SELECT SQL_CALC_FOUND_ROWS  p.`virtuemart_product_id`, l.product_name
                FROM `xxxxx_virtuemart_prod |
| 554353 | azeeda_L505b60 | localhost | azeeda_AZ_live_20181120 | Query   |  103 | Sending data              | SELECT SQL_CALC_FOUND_ROWS  p.`virtuemart_product_id`, l.product_name
                FROM `xxxxx_virtuemart_prod |
| 554356 | azeeda_L505b60 | localhost | azeeda_AZ_live_20181120 | Query   |  103 | Sending data              | SELECT SQL_CALC_FOUND_ROWS  p.`virtuemart_product_id`, l.product_name
                FROM `xxxxx_virtuemart_prod |
| 554421 | azeeda_L505b60 | localhost | azeeda_AZ_live_20181120 | Query   |   96 | Sending data              | SELECT SQL_CALC_FOUND_ROWS  p.`virtuemart_product_id`, l.product_name
                FROM `xxxxx_virtuemart_prod |
| 554422 | azeeda_L505b60 | localhost | azeeda_AZ_live_20181120 | Query   |   95 | Sending data              | SELECT SQL_CALC_FOUND_ROWS  p.`virtuemart_product_id`, l.product_name
                FROM `xxxxx_virtuemart_prod |
| 554426 | azeeda_L505b60 | localhost | azeeda_AZ_live_20181120 | Query   |   95 | Sending data              | SELECT SQL_CALC_FOUND_ROWS  p.`virtuemart_product_id`, l.product_name
                FROM `xxxxx_virtuemart_prod |
| 554778 | azeeda_L505b60 | localhost | azeeda_AZ_live_20181120 | Query   |   58 | Sending data              | SELECT SQL_CALC_FOUND_ROWS  p.`virtuemart_product_id`, l.product_name
                FROM `xxxxx_virtuemart_prod |
| 554937 | azeeda_L505b60 | localhost | azeeda_AZ_live_20181120 | Query   |   39 | Sending data              | SELECT SQL_CALC_FOUND_ROWS  p.`virtuemart_product_id`, l.product_name
                FROM `xxxxx_virtuemart_prod |
| 554938 | azeeda_L505b60 | localhost | azeeda_AZ_live_20181120 | Query   |   39 | Sending data              | SELECT SQL_CALC_FOUND_ROWS  p.`virtuemart_product_id`, l.product_name
                FROM `xxxxx_virtuemart_prod |
| 554941 | azeeda_L505b60 | localhost | azeeda_AZ_live_20181120 | Query   |   39 | Sending data              | SELECT SQL_CALC_FOUND_ROWS  p.`virtuemart_product_id`, l.product_name
                FROM `xxxxx_virtuemart_prod |
| 555000 | azeeda_L505b60 | localhost | azeeda_AZ_live_20181120 | Query   |   33 | Sending data              | SELECT SQL_CALC_FOUND_ROWS  p.`virtuemart_product_id`, l.product_name
                FROM `xxxxx_virtuemart_prod |
| 555005 | azeeda_L505b60 | localhost | azeeda_AZ_live_20181120 | Query   |   34 | Sending data              | SELECT SQL_CALC_FOUND_ROWS  p.`virtuemart_product_id`, l.product_name
                FROM `xxxxx_virtuemart_prod |
| 555010 | azeeda_L505b60 | localhost | azeeda_AZ_live_20181120 | Query   |   34 | Sending data              | SELECT SQL_CALC_FOUND_ROWS  p.`virtuemart_product_id`, l.product_name
                FROM `xxxxx_virtuemart_prod |
| 555217 | azeeda_L505b60 | localhost | azeeda_AZ_live_20181120 | Query   |   12 | Sending data              | SELECT SQL_CALC_FOUND_ROWS  p.`virtuemart_product_id`, l.product_name
                FROM `xxxxx_virtuemart_prod |
| 555346 | azeeda_L505b60 | localhost | azeeda_AZ_live_20181120 | Sleep   |    0 |                           | NULL                                                                                                 |
+--------+----------------+-----------+-------------------------+---------+------+---------------------------+------------------------------------------------------------------------------------------------------+
144 rows in set (0.00 sec)

I've also attached a screen grab of the graph showing the server load.
Title: Re: Where does this query come from?
Post by: behemoth on November 23, 2018, 11:09:35 AM
From research and investigation of this issue it looks like these queries are locking the tables and causing a bottleneck. Is it practical or even desirable to change tables from MyISAM to innodb and exploit the row-locking nature of that engine?

We really need to get to the bottom of this and I don't really know where to turn. Is there anybody who can help with this sort of forensic investigation? We can pay.
Title: Re: Where does this query come from?
Post by: behemoth on November 26, 2018, 09:35:35 AM
Ok, I think I know what's going on.

The Joomla .htaccess is rewriting requests for images that come from the Amazon aranhabot (we upload loads of products to Amazon Marketplace too). These requests appear to be triggering this query but with a categoryid of -2, presumably because there's no actual visitor to the site and no category selected. At peak, this bot can visit many times per second, and the query takes time because we have so many products.

Not sure if this is a bug or whatever but I've had to hack the core (if someone could address this issue in a future version I'd be very grateful) in administrator/components/com_virtuemart/models/product.php

around line 789
was:

$product_ids = $this->exeSortSearchListQuery (2, $select, $joinedTables, $whereString, $groupBy, $orderBy, $filterOrderDir, $nbrReturnProducts);

now:

if($virtuemart_category_id!=-2) {
$product_ids = $this->exeSortSearchListQuery (2, $select, $joinedTables, $whereString, $groupBy, $orderBy, $filterOrderDir, $nbrReturnProducts);
}
else
{
$product_ids=array(1);
}


I'm sure this isn't the best approadh; I'm certainly no expert coder but it does appear to work.
Title: Re: Where does this query come from?
Post by: AH 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
Title: Re: Where does this query come from?
Post by: themhz 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"
Title: Re: Where does this query come from?
Post by: 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.
Title: Re: Where does this query come from?
Post by: themhz on December 18, 2018, 13:56:54 PM
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?
Title: Re: Where does this query come from?
Post by: Studio 42 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.
Title: Re: Where does this query come from?
Post by: themhz 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.
Title: Re: Where does this query come from?
Post by: Studio 42 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.
Title: Re: Where does this query come from?
Post by: themhz 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 ??
Title: Re: Where does this query come from?
Post by: Studio 42 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).