WooCommerce Auto Apply Coupon Extensive Query resulting in performance issues

  • On a large Database, when the number of visitors increases on the website, the auto apply coupon triggers a query that runs every time user adds a product to the cart, this multiple parallel execution of query is degrading the performance of the website.

    SELECT post_id, meta_key, meta_value FROM wp_postmeta WHERE post_id IN (?,?,?) ORDER BY meta_id ASC

    called at





    in mysqli_query called at /nas/content/live/wordpress/wp-includes/class-wpdb.php (2349)
    in wpdb::_do_query called at /nas/content/live/wordpress/wp-includes/class-wpdb.php (2263)
    in wpdb::query called at /nas/content/live/wordpress/wp-includes/class-wpdb.php (3146)
    in wpdb::get_results called at /nas/content/live/wordpress/wp-includes/meta.php (1177)
    in update_meta_cache called at /nas/content/live/wordpress/wp-includes/post.php (7411)
    in update_postmeta_cache called at /nas/content/live/wordpress/wp-includes/post.php (7798)
    in _prime_post_caches called at /nas/content/live/wordpress/wp-includes/class-wp-query.php (3334)
    in WP_Query::get_posts called at /nas/content/live/wordpress/wp-includes/class-wp-query.php (3824)
    in WP_Query::query called at /nas/content/live/wordpress/wp-includes/class-wp-query.php (3956)
    in WP_Query::__construct called at /nas/content/live/wordpress/wp-content/plugins/woocommerce-auto-added-coupons/includes/plugins/WJECF_Autocoupon.php (1052)
    in WJECF_Autocoupon::get_all_auto_coupons called at /nas/content/live/wordpress/wp-content/plugins/woocommerce-auto-added-coupons/includes/plugins/WJECF_Autocoupon.php (831)
    in WJECF_Autocoupon::get_valid_auto_coupons called at /nas/content/live/wordpress/wp-content/plugins/woocommerce-auto-added-coupons/includes/plugins/WJECF_Autocoupon.php (869)
    in WJECF_Autocoupon::get_valid_auto_coupon_codes called at /nas/content/live/wordpress/wp-content/plugins/woocommerce-auto-added-coupons/includes/plugins/WJECF_Autocoupon.php (784)
    in WJECF_Autocoupon::remove_unmatched_autocoupons called at /nas/content/live/wordpress/wp-content/plugins/woocommerce-auto-added-coupons/includes/plugins/WJECF_Autocoupon.php (683)
    in WJECF_Autocoupon::action_woocommerce_check_cart_items called at /nas/content/live/wordpress/wp-includes/class-wp-hook.php (322)
    in WP_Hook::apply_filters called at /nas/content/live/wordpress/wp-includes/class-wp-hook.php (348)
    in WP_Hook::do_action called at /nas/content/live/wordpress/wp-includes/plugin.php (517)
    in do_action called at /nas/content/live/wordpress/wp-content/plugins/woocommerce/includes/shortcodes/class-wc-shortcode-cart.php (93)
    in WC_Shortcode_Cart::output called at /nas/content/live/wordpress/wp-content/plugins/woocommerce/includes/class-wc-shortcodes.php (72)
    in WC_Shortcodes::shortcode_wrapper called at /nas/content/live/wordpress/wp-content/plugins/woocommerce/includes/class-wc-shortcodes.php (85)
    • The table was retrieved with this index: post_id
    • MySQL had to do an extra pass to retrieve the rows in sorted order, which is a cause of poor performance but sometimes unavoidable.

    Is there a way to optimize the query or if the support can provide a way via action and/or filter so that third-parties can avoid the expensive query and do their own computation if they want.

Viewing 10 replies - 1 through 10 (of 10 total)
  • Plugin Author Soft79

    (@josk79)

    How long does the query take? Do you have information on how you profiled this? Do you have many auto coupons?

    If ‘high performance’ mode is enabled on our plugin, the query is only called on a change in the cart. This shouldn’t have a large performance impact.

    Thread Starter Kuldeep Upreti

    (@kuldeepupreti)

    A single query takes around 10 seconds to complete. We monitored this by monitoring tool – New Relic. Currently, we have 3 auto-coupons in the system.

    Yes, the ‘high performance’ mode is enabled on the plugin settings.

    Plugin Author Soft79

    (@josk79)

    What is the query that is executed? It should look like this (xxx is the table prefix)


    SELECT xxxposts.*
    FROM xxxposts
    INNER JOIN xxxpostmeta
    ON ( xxxposts.ID = xxxpostmeta.post_id )
    WHERE 1=1
    AND ( ( xxxpostmeta.meta_key = '_wjecf_is_auto_coupon'
    AND xxxpostmeta.meta_value = 'yes' ) )
    AND xxxposts.post_type = 'shop_coupon'
    AND ((xxxposts.post_status = 'publish'))
    GROUP BY xxxposts.ID
    ORDER BY xxxposts.post_title ASC

    What is the result if you run the query prefixed by EXPLAIN ?

    Plugin Author Soft79

    (@josk79)

    I see you already mentioned the query in your starting post.

    How long does the query take if you execute it directly in your database? What result do you get if you execute the query with EXPLAIN before it?

    EXPLAIN SELECT post_id, meta_key, meta_value FROM wp_postmeta WHERE post_id IN (?,?,?) ORDER BY meta_id ASC

    Replace the questionmarks with the post ids of your auto-coupons.

    Thread Starter Kuldeep Upreti

    (@kuldeepupreti)

    I executed the query directly on the database. It took around 3 seconds to execute the query.

    Here is a JSON for the result, when the query is executed with addition of EXPLAIN before it.

    {
    “id”: 1,
    “select_type”: “SIMPLE”,
    “table”: “wp_postmeta”,
    “partitions”: null,
    “type”: “range”,
    “possible_keys”: “post_id”,
    “key”: “post_id”,
    “key_len”: “8”,
    “ref”: null,
    “rows”: 191841,
    “filtered”: 100.00,
    “Extra”: “Using index condition; Using filesort”
    }

    Plugin Author Soft79

    (@josk79)

    It looks like your wp_postmeta table is missing an INDEX on column meta_key.

    Thread Starter Kuldeep Upreti

    (@kuldeepupreti)

    I think this should be done automatically by wordpress right? Do I need to create an Index for meta_key column?

    Plugin Author Soft79

    (@josk79)

    Yes, it should’ve been done automatically by WP. Yes you need to create an index for meta_key column.

    Maybe this page helps (I have not verified this, so run at your own risk. Create a backup of your database first!): WordPress database index fixer by ManagedWPHosting.nl – Add indexes, keys, primary keys again to your WordPress database tables

    @soft79 Using the index fixer will probably NOT fix this performance issue. It was ment to re-add WP Core indexes that went missing.

    I am the owner of managedwphosting.nl and I wrote that script you refer to.

    Plugin Author Soft79

    (@josk79)

    Hi @ramon-fincken, thanks for looking at this!

    I think the performance issue is related to a missing meta_key index. So this line should do the trick:

    ALTER TABLE 6703b96173268_postmeta ADD KEY meta_key (meta_key(191));

    (where 6703b96173268_ is the table prefix)

Viewing 10 replies - 1 through 10 (of 10 total)
  • You must be logged in to reply to this topic.