Slow DB Query

  • edat

    (@edat)


    3 years, 9 months ago

    Hi Team

    Every time I open open a product to edit in the back end, the YMM plugin runs the following SQL Statement and it take around 7-9 seconds to run and returns the same values for every product I edit.

    SELECT DISTINCT IF(LENGTH(postmeta.meta_value)>0, postmeta.meta_value, postmeta.post_id) as product_sku, ymm.make, ymm.model, ymm.year_from, ymm.year_to
FROM wp_posts AS posts JOIN wp_postmeta AS postmeta
ON postmeta.post_id = posts.ID
AND postmeta.meta_key = '_sku' JOIN wp_ymm ymm
ON ymm.product_id = posts.ID
WHERE posts.post_type = 'product'
ORDER BY posts.ID
LIMIT 1
    product_sku    make                model                  year_from    year_to
 DP900          HARLEY DAVIDSON     1340 FLHR Road King    1994         1994

    The Caller of SQL is Pektsekye_Ymm_Model_Db->getVehicleData()

    Can anyone tell me why this SQL is executed and returns the same value every time no matter what product I’m editing. I’m wondering if there is some type of error, it just seems to take way to long to open a product to edit.

    • This topic was modified 3 years, 9 months ago by edat.
    • This topic was modified 3 years, 9 months ago by edat.
Viewing 3 replies - 1 through 3 (of 3 total)
  • Thread Starter edat

    (@edat)

    3 years, 9 months ago

    Never mind my question about the same product being selected, that was my mistake, but it is taking upwards of 10 seconds to get a result.

    My wp_ymm table has about 296,000 rows.

    Plugin Author Pektsekye

    (@pektsekye)

    3 years, 9 months ago

    Hello,

    >it just seems to take way to long to open a product to edit.

    Try to replace the function hasYmmData() with this:

    
  public function hasYmmData() 
  {
    return $this->_db->hasVehicleData();
  }

    in the files:
    wp-content/plugins/ymm-search/Block/Adminhtml/Product/Edit/Restriction.php
    wp-content/plugins/ymm-search/Block/Adminhtml/Ymm/Selector.php

    Then add a new function:

    
public function hasVehicleData()     
    {
      $value = $this->_wpdb->get_var("SELECT EXISTS (SELECT 1 FROM {$this->_mainTable})");
      return $value == 1;         
    }

    to the file:
    wp-content/plugins/ymm-search/Model/Db.php

    Stanislav

    Thread Starter edat

    (@edat)

    3 years, 9 months ago

    Thank you, that have speed up the process.

Viewing 3 replies - 1 through 3 (of 3 total)

The topic ‘Slow DB Query’ is closed to new replies.