News:

Support the VirtueMart project and become a member

Main Menu

Huge SQL queries

Started by sandomatyas, November 10, 2018, 20:44:07 PM

Previous topic - Next topic

sandomatyas

Some crawler bot found a site of mine and within few hours it generates >2GB of slow queries log.
There was a lot of queries like this: https://prnt.sc/lgrf1y
Is it possible to optimize these somehow?

GJC Web Design

GJC Web Design
VirtueMart and Joomla Developers - php developers https://www.gjcwebdesign.com
VM4 AusPost Shipping Plugin - e-go Shipping Plugin - VM4 Postcode Shipping Plugin - Radius Shipping Plugin - VM4 NZ Post Shipping Plugin - AusPost Estimator
Samport Payment Plugin - EcomMerchant Payment Plugin - ccBill payment Plugin
VM2 Product Lock Extension - VM2 Preconfig Adresses Extension - TaxCloud USA Taxes Plugin - Virtuemart  Product Review Component
https://extensions.joomla.org/profile/profile/details/67210
Contact for any VirtueMart or Joomla development & customisation

sandomatyas

I digged a bit and found this in VirtueMartModelProduct
//This option switches between showing products without the selected language or only products with language.
if( $app->isSite() ){ //and !VmConfig::get('prodOnlyWLang',false)){
if((empty($this->keyword) or $group !== FALSE) and self::$omitLoaded and self::$_alreadyLoadedIds){
$where[] = ' p.`virtuemart_product_id`!='.implode(' AND p.`virtuemart_product_id`!=',self::$_alreadyLoadedIds).' ';
//$where[] = ' p.`virtuemart_product_id` NOT IN ('.implode(',',self::$_alreadyLoadedIds).') ';
}

} else {
//$joinLang = true;
}


When I removed this code part, the server worked fine.
As far as I can see, it's mod_virtuemart_products. I disabled 'omitLoaded' in every module, I don't need it actually, but maybe it worth to check. I'm not sure WHY the 'NOT IN' version was replaced to this

Studio 42

NOT IN is limited to a certain number of data, it's perhaps why they removed it.
But internally, if i'm right, Mysql change NOT IN to the appropriate query AND `virtuemart_product_id`!= ... so it's strange that one do a slow query and not the other one

Milbo

changed in the new core vm3.4.3 to use not in and has doubled speed in case a lot products got already loaded.
Should I fix your bug, please support the VirtueMart project and become a member
______________________________________
Extensions approved by the core team: http://extensions.virtuemart.net/