0

I have a very slow query in woocommerce.

SELECT post_id, meta_key, meta_value
FROM wp_postmeta
WHERE post_id IN (116263,116253,116237,116197,116187,116177,116167,116157,116147,116137,116127,116117,116107,116097,116087,116079,116071,116063,116055,116047,116039,116031,116023,116015,116007,115999,115991,115983,115975,115967,115959,115951,115943,115935,115927,115919,115911,115903,115893,115883,115873,115863,115853,115843,115833,115825,115815,*****)

total rows 513394

component Plugin: woocommerce

Caller

  • do_action('wp_loaded')
  • wp-includes/plugin.php:453
  • WC_Cart_Session->get_cart_from_session()
  • wp-content/plugins/woocommerce/includes/class-wc-cart-session.php:95
  • WC_Product->is_purchasable()
  • wp-content/plugins/woocommerce/includes/abstracts/abstract-wc-product.php:1458
  • WC_Product->get_price()
  • wp-content/plugins/woocommerce/includes/abstracts/abstract-wc-product.php:275
  • WC_Data->get_prop()
  • wp-content/plugins/woocommerce/includes/abstracts/abstract-wc-data.php:694 apply_filters('woocommerce_product_get_price')

time 1.0919

Is there a way to reduce query time?

2

Yep, three things.

A) You have bot or an a**hole webscraper crawling your site and making a ton of bunk checkout caches. You need to invest in a web application firewall like Sucuri or Wordfence (use the paid version, not the free one in either case). If you don't do this, you are going to have this problem again, and again, and again ad infinitum. You are also going to want to check your server's access logs and block any IP addresses that have a ton of requests in a very short timeline unless they are google, bing, yandex, or another search provider you care about for your SEO. If they aren't, they are either sniffing your site for holes to steal money, or are trying to steal your catalog/image/etc because they are running a clone shop and they are to lazy to lay it out themselves. Either way they need to be blocked, and the WAF (Sucuri or Wordfence) will insure they get blocked again quickly when they inevitably change their IP and try again.

B) You need to flush your cache. The following query should do ya pretty good:

DELETE FROM wp_options
WHERE option_name LIKE '_wc_session_%' OR option_name LIKE '_wc_session_expires_%'

Some of your legit customers might grumble slightly. However if you don't do this, your website will eventually crash and your database will get corrupted and irrecoverable, which is way worse. Run a SQL dump before you do this if you want a backup, but caches are pretty much trash anyhow, so it's not that important.

C) You need to make sure that wp cron is working correctly so the caches flush on an appropriate schedule. Make sure that the following line does not exist anywhere on your installation:

define('DISABLE_WP_CRON', 'true');

You can grep for it to do that quickly.


To make sure this doesn't become a recurring problem, it would be wise to set up a CDN like CloudFlare or CloudFront to cache all of your product pages but not the checkout, and not forward headers or cookies to your site for the products unless they are not currently cached. You then set up Woo to only allow the cart for logged in users, which makes it inaccessible to bots. It is usually a good idea to let people login with google or facebook if you do this so you don't get too many bounces from customers who don't like the hassle of logging in.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.