Author Topic: Huge SQL queries  (Read 146 times)

sandomatyas

  • Jr. Member
  • **
  • Posts: 233
Huge SQL queries
« on: November 10, 2018, 20:44:07 pm »
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

  • 3rd party VirtueMart Developer
  • Super Hero
  • *
  • Posts: 8434
  • Virtuemart, Joomla & php developer
    • GJC Web Design
  • VirtueMart Version: 2.6.22 & 3.2.14
Re: Huge SQL queries
« Reply #1 on: November 10, 2018, 21:41:43 pm »
same queries here .. he thinks its from the inventory view

http://forum.virtuemart.net/index.php?topic=141361
GJC Web Design
VirtueMart and Joomla Developers - php developers http://www.gjcwebdesign.com
VM3 AusPost Shipping Plugin - e-go Shipping Plugin - VM3 Postcode Shipping Plugin - Radius Shipping Plugin - VM3 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
http://extensions.joomla.org/profile/profile/details/67210
Contact for any VirtueMart or Joomla development & customisation

sandomatyas

  • Jr. Member
  • **
  • Posts: 233
Re: Huge SQL queries
« Reply #2 on: November 19, 2018, 13:01:07 pm »
I digged a bit and found this in VirtueMartModelProduct
Code: [Select]
//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

  • Contributing Developer
  • Sr. Member
  • *
  • Posts: 3468
  • Joomla & Virtuemart addon developper
    • Studio 42 - Virtuemart & Joomla extentions
  • VirtueMart Version: 2.6 & 3.0.x.y
Re: Huge SQL queries
« Reply #3 on: November 19, 2018, 13:06:29 pm »
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

  • Virtuemart Projectleader
  • Administrator
  • Super Hero
  • *
  • Posts: 9726
  • VM3.2 Cached and Optimized
    • VM3 Extensions
  • VirtueMart Version: VirtueMart 3 on joomla 3
Re: Huge SQL queries
« Reply #4 on: November 19, 2018, 13:50:11 pm »
changed in the new core vm3.4.3 to use not in and has doubled speed in case a lot products got already loaded.
I should fix your bug, please support the VirtueMart project and become a member
______________________________________
Extensions approved by the core team: http://extensions.virtuemart.net/